Horton

Re: remaining sql/json patches

amit <amitlangote09@gmail.com>

View thread
From: Amit Langote <amitlangote09@gmail.com>
To: jian he <jian.universality@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-08-04T12:21:49Z
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.

Attachments

Hi,

On Sun, Jul 23, 2023 at 5:17 PM jian he <jian.universality@gmail.com> wrote:
> hi
> based on v10*.patch. questions/ideas about the doc.

Thanks for taking a look.

> > 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.

OK, will change the text to say that the default ON ERROR behavior is
to return false.

> --------------------------------------------------------------------------------------------------
> /* --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.

ON SCALAR STRING is just syntactic sugar.  KEEP/OMIT QUOTES specifies
the behavior when the result of JSON_QUERY() is a JSON scalar value.

> 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 ...

Hmm, yes, I think the code that produces the error is not trying hard
enough to figure out the actually specified QUOTES clause.  Fixed and
added new tests.

> --------------------------------------------------------------------------------
> 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.

I suppose you mean the following case:

SELECT JSON_QUERY(jsonb '[1,2]', '$[*]');
 json_query
------------

(1 row)

which with ERROR ON ERROR gives:

SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' ERROR ON ERROR);
ERROR:  JSON path expression in JSON_QUERY should return singleton
item without wrapper
HINT:  Use WITH WRAPPER clause to wrap SQL/JSON item sequence into array.

The default return value for JSON_QUERY when an error occurs during
path expression evaluation is NULL.  I don't think that it needs to be
mentioned separately.

> ------------------------------------------------------------------------------------
> 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 ?

OK, I've improved the text here.

> -------------------------------------------------------------------------------------
> > You can have only one ordinality column per table
> but the regress test shows that you can have more than one ordinality column.

Hmm, I am not sure why the code's allowing that.  Anyway, for the lack
any historical notes on why it should be allowed, I've fixed the code
to allow only one ordinality columns and modified the tests.

> ----------------------------------------------------------------------------
> 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.

OK, done.

I'm still finding things to fix here and there, but here's what I have
got so far.




--
Thanks, Amit Langote
EDB: http://www.enterprisedb.com