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

That fixes the first of my problems. Thank you. I'll rewrite the question to avoid confusing people. Any thoughts on the other two? — Abraham P, Nov 18 '14 at 09:00
Why would you try to convert a phone to integer?! The value 5555555555 for example is **not** an integer, since it exceeds the (2^31)-1 limit. — Kouber Saparev, Nov 18 '14 at 09:47
trying to convert from a bigint to a string. Trying to allow human notation eg. + for country code, parens for area code et al — Abraham P, Nov 18 '14 at 09:50
@KouberSaparev Not to mention `+61 8 # 9999 9999 *`. Phone numbers aren't just numbers. — Craig Ringer, Nov 18 '14 at 09:51
@CraigRinger you are 100% correct kinda didn't really think that one through. In either case now trying to convert to the right format. Unfortunately my SQL is..... challenged — Abraham P, Nov 18 '14 at 09:52
I do not get the whole idea. Why would you check whether something is a number, just to convert it to a string then?... The value type of a JSON value is already a varchar (a.k.a. string). — Kouber Saparev, Nov 18 '14 at 09:55

score 1 · Accepted Answer · edited May 23 '17 at 11:49

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. :-)
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;
```
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?

score 0 · Answer 2 · answered Feb 18 '23 at 07:52

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

Check the value type of a JSON value in Postgres

2 Answers2