I am trying to split different topics to different email to keep every issue to be focused.
Sure!
I also have a suggestion.
If I do:
```
— s1
select (t.data)['con']['a'][1]['b']['c']['d'] from test_jsonb_types t;
—s2
select (t.data).con.a[1].b['c'].d from test_jsonb_types t;
```
The two statements are actually identical. But they generate quite different rewritten query trees. S1’s rewritten tree is much simpler than s2’s. However, their plan trees are the same.
The above two statements are NOT identical. Specifically, dot-notation
(e.g., .con) and pre-standard jsonb subscripting (e.g., ['con']) are
NOT semantically the same.
Here's an example:
-- setup
create table t (jb jsonb);
insert into t SELECT '{"con": 1}'::jsonb;
insert into t SELECT '[{"con": 1}, {"con": {"a": 2}}]'::jsonb;
-- queries
test=# select (t.jb).con from t;
con
---------------
1
[1, {"a": 2}]
(2 rows)
test=# select (t.jb)['con'] from t;
jb
----
1
(2 rows)
As you can see, dot-notation returns different results from jsonb
subscripting.
As I mentioned in the previous reply:
The SQL standard states that simplified access is equivalent to:
JSON_QUERY (VEP, 'lax $.JC' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR
)
where:
VEP = <value expression primary>
JC = <JSON simplified accessor op chain>
And
In lax mode:
— If an operation requires an SQL/JSON array but the operand is not an SQL/JSON array, then the operand is first “wrapped” in an SQL/JSON array prior to performing the operation.
— If an operation requires something other than an SQL/JSON array, but the operand is an SQL/JSON array, then the operand is “unwrapped” by converting its elements into an SQL/JSON sequence prior to performing the operation.
— After applying the preceding resolutions to structural errors, if there is still a structural error , the result is an empty SQL/JSON sequence.
The example query demonstrates the second point above. The
dot-notation attempts to access a member field (."con") of a JSON
object, while the operand is a JSON array ([{"con": 1}, {"con": {"a":
2}}]). In "lax" mode, the operand is "unwrapped" into a JSON sequence
(two elements: {"con": 1} and {"con": {"a": 2}}), and the member field
access is performed on each element. The multiple results are then
wrapped into a JSON array ([1, {"a": 2}]) due to WITH CONDITIONAL
ARRAY WRAPPER. I’ve already explained what "ARRAY WRAPPER" does in my
previous reply, so I won't repeat it here.
Best,
Alex
