jsonpostgresqlpostgresql-json

Check the value type of a JSON value in Postgres

Let's say I have a json column fields, like so:

 {phone: 5555555555, address: "55 awesome street", hair_color: "green"}

What I would like to do is update all entries where the json key phone is present, and the result is of type number to be a string.

What I have is: 

 SELECT *
 FROM parent_object
 WHERE (fields->'phone') IS NOT NULL;

Unfortunately this still returns values where phone:null. I'm guessing that a JSON null is not equivalent to a SQL NULL.

How do I 1) How do I rule out JSON nulls AND (fields->'phone') <> null produces

 LINE 4: ...phone') IS NOT NULL AND (fields->'phone') <> 'null';
 HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

2) Check the type of the value at that key, this pseudocode (type_of (fields->'phone') == Integer) but in working PGSQL.

3) Modify this to update the column

 UPDATE parent_object
 SET fields.phone = to_char(fields.phone)
 WHERE  query defined above
Solution

    1. As other folks have said, there is no reason to convert the variable to an integer just to them cast it to a string. Also, phone numbers are not numbers. :-)

    2. You need to be using the ->> operator instead of ->. That alongside IS NOT NULL gets your SELECT query working.

      Note the difference between the two tuple values after running this query:

      SELECT fields->'phone', fields->>'phone'
FROM parent_object;

      Your working query:

      SELECT *
FROM parent_object
WHERE (fields->>'phone') IS NOT NULL;

    3. Postgres does not currently natively support atomically updating individual keys within a JSON column. You can write wrapper UDFs to provide this capability to you: How do I modify fields inside the new PostgreSQL JSON datatype?