Search code examples
postgresqljsonb

Explain parameter 'path' (text[]) of postgres function jsonb_set

Which values are valid for the path text[] parameter of the jsonb_set function and how are they interpreted? Numbers vs. strings vs. something else?

jsonb_set(target         jsonb,
          path           text[], /* <-- ??? */
          new_value      jsonb,
          create_missing boolean default true)

Especially if the queried json is a complex object like {"Id": 1, "Values": {"Name": "Doe", "Online": [10,20,30]}} and not a simple array.

obj = '{"Id": 1, "Values": {"Name": "Doe", "Online": [10,20,30]}}'

SELECT jsonb_set(obj, '{Values,0}', '99');
-> {"Id": 1, "Values": {"0": 99, "Name": "Doe", "Online": [10, 20, 30]}}

SELECT jsonb_set(obj, '{Values,Online,0}', '99');
-> {"Id": 1, "Values": {"Name": "Doe", "Online": [99, 20, 30]}}

And yes i read the docu here, but they always only say "Returns target with the item designated by path" - and how is "path" defined, works?

Solution

  • That is pretty clearly described in the documentation of the #> operator, where you can find

    the specified path, where path elements can be either field keys or array indexes

    Admittedly, that information is not repeated in the description of jsonb_set().