postgresql Json path capabilities

Question

In documentation some of the postgresql json functions uses a json path attribute. For exemple the jsonb_set function :

jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean])

I can't find any of the specifications of this type of attribute.

Can it be used for example to retrieve an array element based on it's attribute's value ?

Answer 1

The path is akin to a path on a filesystem: each value drills further down the leaves of the tree (in the order you specified). Once you get a particular JSONB value from extracting it via a path, you can chain other JSONB operations if needed. Using functions/operators with JSONB paths is mostly useful for when there are nested JSONB objects, but can also handle simple JSONB arrays too.

For example:

SELECT '{"a": 42, "b": {"c": [1, 2, 3]}}'::JSONB #> '{b, c}' -> 1;

...should return 2.

The path {b, c} first gets b's value, which is {"c": [1, 2, 3]}. Next, it drills down to get c's value, which is [1, 2, 3]. Then the -> operation is chained onto that, which gets the value at the specified index of that array (using base-zero notation, so that 0 is the first element, 1 is the second, etc.). If you use -> then it will return a value with a JSONB data type, whereas ->> will return a value with a TEXT data type.

But you could have also written it like this:

SELECT '{"a": 42, "b": {"c": [1, 2, 3]}}'::JSONB #> '{b, c, 1}';

...and simply included both keys and array indexes in the same path.

For arrays, the following two should be equivalent, except the first uses a path, and the second expects an array and gets the value at the specified index:

SELECT '[1, 2, 3]'::JSONB #> '{1}';

SELECT '[1, 2, 3]'::JSONB -> 1;

Notice a path must always be in JSON array syntax, where each successive value is the next leaf in the tree you want to drill down to. You supply keys if it is a JSONB object, and indexes if it is a JSONB array. If these were file paths, the JSONB keys are like folders, and array indexes are like files.