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.

Because @? supports SQL/JSON path and #> does not. jsonb_set() and #> were introduced before Postgres supported SQL/JSON path (I think even before SQL/JSON path was included into the SQL standard) — user330315
– user330315, Commented Feb 25, 2021 at 6:45

Laurenz Albe · Accepted Answer · 2021-02-25 03:28:20Z

4

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.

answered Feb 25, 2021 at 3:28

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

FireEmerald Over a year ago

What operators are valid for the test[] parameter of jsonb_set function and how do they work? I couldn't find a list, only saw in examples that a number seems to mean item at index and a string a property in an object, anything else?

Laurenz Albe Over a year ago

@FireEmerald That looks like a new question.

FireEmerald Over a year ago

Okay, see stackoverflow.com/q/77986770/4300087

lebolo · Accepted Answer · 2021-02-25 13:08:27Z

Thanks for the answers and comments about why the data types were different.

I wanted to post how I solved my problem:

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.

WITH unnested AS (
    SELECT f.pk, JSONB_ARRAY_ELEMENTS(f.json_obj -> 'values') AS value
    FROM foo f
),
updated_values AS (
    SELECT un.pk, JSONB_SET(un.value, '{id}', TO_JSONB(LOWER(REGEXP_REPLACE(un.value ->> 'id', '[^a-zA-Z0-9]', '', 'g'))), FALSE) AS new_value
    FROM unnested un
    WHERE value -> 'id' IS NOT NULL -- Had some values that didn't have 'id' keys
)
UPDATE foo f2
SET json_obj = JSONB_SET(f2.json_obj, '{values}', (SELECT JSONB_AGG(uv.new_value) FROM updated_values uv WHERE uv.pk = f2.pk), FALSE)
WHERE JSONB_PATH_EXISTS(f2.json_obj, '$.values[*].id') -- Had some values that didn't have 'id' keys

Collectives™ on Stack Overflow

Different path formats for PostgreSQL JSONB functions

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related