No description of json_query($..) behavior in documentation

[DenGodunov]

Hello, found useful to use .. inside json_query(A, 'strict $..B..C.D' with array wrapper) but didn't find any description of it's behavior to point out my collaegues in https://trino.io/docs/current/functions/json.html#id6 . Could you please add article or send me a ink if it already exists?

[XavieLee]

hi @DenGodunov, I don't understand what you want the document to describe, can you share more or give me some examples?

[DenGodunov]

Hi, as I understand two dots in json path behave as "starting from the input, it recursively descends and produces every value it can find" so as an example it could be select json_query("some json path with two dots") to json object which illustrates this behavior.
For example:
select json_query('{
"id" : 1,
"notes" : [{"type" : 1, "comment" : "foo"}, {"type" : 2, "comment" : null}],
"comment" : ["bar", "baz"]
}',
'strict $..comment'
with array wrapper)
returns [["bar","baz"],"foo",null]

[DenGodunov]

@XavieLee why this select returns NULL instead of value 'Diana'?
select json_query('{
"author" : "Diana",
"id" : {"value" : 1, "author" : "John"},
"notes" : [{"type" : 1, "comment" : "foo"}, {"type" : 2, "comment" : null}],
"comment" : ["bar", "baz"]
}',
'strict $..author'
omit quotes
)
and why this example returns 100 and not 1:
select json_query('{
"b":1,
"b":100
}',
'strict $..b'
omit quotes
)

[XavieLee]

@DenGodunov
For case 1,
After testing, I found when using <path>..key method, json_query output matches the expected result if you specify WITH ARRAY WRAPPER. However, without WITH ARRAY WRAPPER, json_query returns NULL. I'm unsure whether this is a bug or the documentation is not clear.

@kasiafi Could you help clarify this issue?

For case 2
This is not valid JSON, or rather, this JSON is effectively equivalent to:

{
   "b": 100
}

Therefore, the query result will always be 100.

[DenGodunov]

@XavieLee so there is no documentation regarding all these cases is it possible to add small article regarding "..key" method?
It could be:
1 with array wrapper
2 without array wrapper
2.1 when duplicate keys on one level (always return last value)
2.2. when duplicate keys on different levels - returns null (bug of feature)

[XavieLee]

@DenGodunov I think so too. I could only find some documentation about this here. descendant member accessor

[DenGodunov]

@XavieLee regarding case which returns NULL there is a description like this https://jqlang.org/manual/v1.6/#recursive-descent
... Note that ..a does not work; use .. | .a instead. May be there is a way to use these approach in Trino?

[XavieLee]

@DenGodunov Thanks for ur input, I'm also waiting for kasiafi's reply. I will complete the documentation after figure out the issue.

[DenGodunov]

@XavieLee any update on this?

[XavieLee]

Hi @DenGodunov , I tried to figure out the underlying implementation principle so that I can explain it better, but since I am not the author, if the author can reply to us, this problem can be solved quickly.

[kasiafi]

Hi @DenGodunov and @XavieLee.

Let me first explain what happened to Diana.

Note that in your query, there are two "authors": Diana and John.

select json_query('{
"author" : "Diana",
"id" : {"value" : 1, "author" : "John"},
"notes" : [{"type" : 1, "comment" : "foo"}, {"type" : 2, "comment" : null}],
"comment" : ["bar", "baz"]
}',
'strict $..author'
omit quotes
)

The json_query function finds both of them, but it cannot return two separate items. json_query must return a single JSON item. That's why WITH ARRAY WRAPPER helps: it wraps Diana and John together in one JSON array, which can be then successfully returned.
Why we get NULL otherwise? Because it's the default error result.

There is an example of the above behavior in the docs for json_query.
Please let me know if everything is clear.

And now the duplicate key issue.
Trino implementation does not support duplicate JSON keys. Keys are deduplicated when JSON is parsed. This is consistent with SQL specification. Also, you should not assume which one of the identical keys will be preserved. There's no guarantee to that.

[DenGodunov]

@kasiafi Hi, so in other words using ..key method without ARRAY WRAPPING is not safe to use if there is a possibility of existing same key in different levels of nesting:

1. if identical key names exists in different level of nesting it will return NULL
2. if identical key exists (in the same level of nesting) (impossible real world scenario) it returns the last one's value

[kasiafi]

@DenGodunov

if identical key names exists in different level of nesting it will return NULL

Yes: if there are multiple hits, we need to wrap them in an array.

if identical key exists (in the same level of nesting) (impossible real world scenario) it returns the last one's value

If identical keys exist in the same JSON object, they will be disambiguated during parsing. It is not guaranteed that we'll get the last value. It can be any value associated with the key. Also, note that there can be identical keys on the same level of nesting if they belong to separate JSON objects.

You can choose the error behavior. By default, NULL is returned, but if you want more explicit failures, set ERROR ON ERROR.

Also, note that there are different modes of array wrapping
You can use WITH CONDITIONAL ARRAY WRAPPER which will not wrap if it found a singe JSON array or a single JSON object.