Cockroach: sql: the result column name for json_array_elements should be "value"

[knz]

Found by @drewdeally

 with a_table(jdata) as (
values (
    '{
        "uid":14105529,
        "o":[
            {"mid":6551,"ac":1913,"ip":"144.36.233.44","adw":5,"at":133000,"ad":151015,"aid":0},
            {"mid":7552,"ac":1913,"ip":"144.36.233.44","adw":5,"at":133000,"ad":151015,"aid":0},
            {"mid":7553,"ac":1913,"ip":"144.36.233.44","adw":5,"at":133000,"ad":151015,"aid":0}
        ] }'::jsonb
    )
)
select jdata->'uid' as uid, value
from a_table, jsonb_array_elements(jdata->'o')
where (value->>'mid')::int > 7000;

error:

pq: column "value" does not exist

This is because the result column for json_array_elements is not called "value" by default, unlike in pg.

Workaround is to use jsonb_array_elements(jdata->'o') as value

[knz]

Might be worthwhile to also audit other built-ins for mismatches from pg.

[knz]

cc @RaduBerinde @asubiotto for triage

[RaduBerinde]

Looking at https://www.postgresql.org/docs/12/functions-json.html I see the json[b]_array_elements[_text] variations that return a column name that doesn't match the function.

[RaduBerinde]

The builtin properly defines the return column name, I just don't think we use the ReturnLabels: https://github.com/cockroachdb/cockroach/blob/70717d5d832ca38e31d072b700b26dd3a767f85c/pkg/sql/sem/builtins/generator_builtins.go#L214
https://github.com/cockroachdb/cockroach/blob/70717d5d832ca38e31d072b700b26dd3a767f85c/pkg/sql/sem/builtins/generator_builtins.go#L701

[rytaft]

cc @giorgosp -- If you can fix this easily as part of #41861 that would be great. Otherwise, I'll fix it after you merge that PR.

[giorgosp]

Yes @rytaft I will look into it

[RaduBerinde]

We'll probably want to backport the fix, ideally it would be a separate commit before the rest of the refactoring.

[giorgosp]

Basically, when a json function is in a SELECT, postgres uses the function's name as the column's label. But when the function is in a FROM , "value" is used as the column's label.
https://github.com/postgres/postgres/blob/5264d9154178d3aeaa0359b43a450298a7ce7281/src/test/regress/expected/json.out#L804

How do we want to handle this? Do we want to implement the same exact behavior?

[RaduBerinde]

Yeah we need to do the same (for compatibility).