Is there any documentation for the JSON "array path" notation? "jsonb#>{x, y, z}"

user_10256275 0
Viewed 41

I'm trying to gather information around the "array notation" (for a lack of a better name) that's used to "select"/"target" keys in a JSON blob.

I'm specifically talking about the '{a, b, c}' notation that you can use with the #> operator.

The Postgres documentation gives a couple of usage examples, such as:

'{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}' // {"c": "foo"}

or

'{"a": {"b": [1, 2]}}'::json#>'{a,b,1}' // 2

But nothing beyond these very basic examples. I couldn't even find a proper name for this API.

Here are my questions:

  • Do you know any proper documentation on the subject
  • If not, do you know any features of that API that go beyond the two very simple examples mentioned above? (Meaning: selecting deep keys & selecting an array index) Can it be of any different shape?

We're trying to evaluate whether we can safely parse a string of shape "{a, b, c}" to an array of ["a", "b", "c"] that we could send to our Postgres driver.

Thanks for taking the time to answer me. Cheers!

postgresql
2 Answers

There is no "name" for this, as this is completely "made up" by the Postgres developers.

The #> (or #>>) operator is simply a shorthand for chaining multiple -> operators.

So

'{"a": {"b": [1, 2]}}'::jsonb  #> '{a,b,1}'

is the same as

(('{"a": {"b": [1, 2]}}'::jsonb -> 'a') -> 'b') -> 1

The parentheses aren't really necessary, I just added them to make it more obvious that the second -> uses the result of the first ->

user_3303150

Your question seems to be about the array input syntax, not the #> operator itself. PostgreSQL arrays are a generic feature and used in many places other than in JSON contexts. The input syntax for PostgreSQL arrays is documented. Since PostgreSQL knows that #> takes an array of strings on its RHS, it knows to treat the construct as an array of strings without you needing to add a cast. There are other ways of doing it, for example you could replace '{a,b,1}' with ARRAY['a','b','1'].

Many drivers will provide a way to directly bind an array (or array reference) to a single placeholder. For help specifically with your driver, you would need to identify it.

user_17212390
Related