Check the value type of a JSON value in Postgres

Question

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

Answer 1

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?

Answer 2

For checking the type of the value at key, postgres has the following in the documentation.

json_typeof ( json ) → text jsonb_typeof ( jsonb ) → text

Returns the type of the top-level JSON value as a text string. Possible types are object, array, string, number, boolean, and null. (The null result should not be confused with a SQL NULL; see the examples.)

json_typeof('-123.4') → number

json_typeof('null'::json) → null

json_typeof(NULL::json) IS NULL → t