Different path formats for PostgreSQL JSONB functions

Question

I'm confused by how path uses different formats depending on the function in the PostgreSQL JSONB documentation.

If I had a PostgreSQL table foo that looks like

pk	json_obj
0	{"values": [{"id": "a_b", "value": 5}, {"id": "c_d", "value": 6]}
1	{"values": [{"id": "c_d", "value": 7}, {"id": "e_f", "value": 8]}

Why does this query give me these results?

SELECT json_obj,                                          -- {"values": [{"id": "a_b", "value": 5}, {"id": "c_d", "value": 6]}
       json_obj @? '$.values[*].id',                      -- true
       json_obj #> '$.values[*].id',                      -- ERROR: malformed array literal
       json_obj #> '{values, 0, id}',                     -- "a_b"
       JSONB_SET(json_obj, '$.annotations[*].id', '"hi"') -- ERROR: malformed array literal
FROM foo;

Specifically, why does @? support $.values[*].id (described on that page in another section) but JSONB_SET uses some other path format {bar,3,baz}?

Ultimately, what I would like to do and don't know how, is to remove non-alphanumeric characters (e.g. underscores in this example) in all id values represented by the path $.values[*].id.

Answer 1

The reason is that the operators have different data types on the right hand side.

SELECT oprname, oprright::regtype
FROM pg_operator
WHERE oprleft = 'jsonb'::regtype
  AND oprname IN ('@?', '#>');

 oprname | oprright 
---------+----------
 #>      | text[]
 @?      | jsonpath
(2 rows)

Similarly, the second argument of jsonb_set is a text[].

Now '$.values[*].id' is a valid jsonpath, but not a valid text[] literal.