Hi Chao, On Thu, Aug 28, 2025 at 8:42 PM Chao Li <[email protected]> wrote:
> I am trying to split different topics to different email to keep every > issue to be focused. > Sure! On Thu, Aug 28, 2025 at 8:42 PM Chao Li <[email protected]> wrote: > 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
