Horton

Re: remaining sql/json patches

jian he <jian.universality@gmail.com>

View thread
From: jian he <jian.universality@gmail.com>
To: Amit Langote <amitlangote09@gmail.com>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>, Andrew Dunstan <andrew@dunslane.net>, Erik Rijkers <er@xs4all.nl>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2023-07-23T08:17:25Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. SQL/JSON: Various improvements to SQL/JSON query function docs

  2. SQL/JSON: Fix some obsolete comments.

  3. SQL/JSON: Fix issues with DEFAULT .. ON ERROR / EMPTY

  4. JSON_TABLE: Add support for NESTED paths and columns

  5. Fix JsonExpr deparsing to emit QUOTES and WRAPPER correctly

  6. Fix typo introduced in 6185c9737

  7. Add basic JSON_TABLE() functionality

  8. Avoid splitting errmsg string to span multiple lines

  9. Add SQL/JSON query functions

  10. Implement various jsonpath methods

  11. Add soft error handling to some expression nodes

  12. Adjust populate_record_field() to handle errors softly

  13. Refactor code used by jsonpath executor to fetch variables

  14. Test EXPLAIN (FORMAT JSON) ... XMLTABLE

  15. Simplify productions for FORMAT JSON [ ENCODING name ]

  16. Add trailing commas to enum definitions

  17. doc: add missing <returnvalue> and whitespace

  18. Add more SQL/JSON constructor functions

  19. Rename a nonterminal used in SQL/JSON grammar

  20. Some refactoring to export json(b) conversion functions

  21. Don't include CaseTestExpr in JsonValueExpr.formatted_expr

  22. Code review for commit b6e1157e7d

  23. Pass constructName to transformJsonValueExpr()

  24. Unify JSON categorize type API and export for external use

  25. Make some indentation in gram.y consistent

  26. Allow most keywords to be used as column labels without requiring AS.

  27. Reduce size of backend scanner's tables.

  28. Use perfect hashing, instead of binary search, for keyword lookup.

hi
based on v10*.patch. questions/ideas about the doc.

> json_exists ( context_item, path_expression [ PASSING { value AS varname } [, ...]] [ RETURNING data_type ] [ { TRUE | FALSE | UNKNOWN | ERROR } ON ERROR ])
> Returns true if the SQL/JSON path_expression applied to the context_item using the values yields any items. The ON ERROR clause specifies what is returned if an error occurs. Note that if the path_expression is strict, an error is generated if it yields no items. The default value is UNKNOWN which causes a NULL result.

only SELECT JSON_EXISTS(NULL::jsonb, '$'); will cause a null result.
In lex mode, if yield no items return false, no error will return,
even error on error.
Only  case error will happen, strict mode error on error. (select
json_exists(jsonb '{"a": [1,2,3]}', 'strict $.b' error on error)

so I came up with the following:
Returns true if the SQL/JSON path_expression applied to the
context_item using the values yields any items. The ON ERROR clause
specifies what is returned if an error occurs, if not specified, the
default value is false when it yields no items.
Note that if the path_expression is strict, ERROR ON ERROR specified,
an error is generated if it yields no items.
--------------------------------------------------------------------------------------------------
/* --first branch of json_table_column spec.

name type [ PATH json_path_specification ]
        [ { WITHOUT | WITH { CONDITIONAL | [UNCONDITIONAL] } } [ ARRAY
] WRAPPER ]
        [ { KEEP | OMIT } QUOTES [ ON SCALAR STRING ] ]
        [ { ERROR | NULL | DEFAULT expression } ON EMPTY ]
        [ { ERROR | NULL | DEFAULT expression } ON ERROR ]
*/
I am not sure what " [ ON SCALAR STRING ]"  means. There is no test on this.
i wonder how to achieve the following  query with json_table:
select json_query(jsonb '"world"', '$' returning text keep quotes) ;

the following case will fail.
SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text PATH
'$' keep quotes ON SCALAR STRING ));
ERROR:  cannot use OMIT QUOTES clause with scalar columns
LINE 1: ...T * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text ...
                                                             ^
error should be ERROR:  cannot use KEEP QUOTES clause with scalar columns?
LINE1 should be: SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS
(item text ...
--------------------------------------------------------------------------------
quote from json_query:
> This function must return a JSON string, so if the path expression returns multiple SQL/JSON items, you must wrap the result using the
> WITH WRAPPER clause.

I think the final result will be: if the RETURNING clause is not
specified, then the returned data type is jsonb. if multiple SQL/JSON
items returned, if not specified WITH WRAPPER, null will be returned.
------------------------------------------------------------------------------------
quote from json_query:
>  The ON ERROR and ON EMPTY clauses have similar semantics to those clauses for json_value.
quote from json_table:
> These clauses have the same syntax and semantics as for json_value and json_query.

it would be better in json_value syntax explicit mention: if not
explicitly mentioned, what will happen when on error, on empty
happened ?
-------------------------------------------------------------------------------------
> You can have only one ordinality column per table
but the regress test shows that you can have more than one ordinality column.
----------------------------------------------------------------------------
similar to here
https://git.postgresql.org/cgit/postgresql.git/tree/src/test/regress/expected/sqljson.out#n804
Maybe in file src/test/regress/sql/jsonb_sqljson.sql line 349, you can
also create a  table first. insert corner case data.
then split the very wide select query (more than 26 columns) into 4
small queries, better to view the expected result on the web.