Re: SQL/JSON query functions context_item doc entry and type requirement - Mailing list pgsql-hackers

Hi,

On Tue, Jun 4, 2024 at 12:11 AM jian he <jian.universality@gmail.com> wrote:
>
> hi
> based on gram.y and function transformJsonValueExpr.
>
> gram.y:
> | JSON_QUERY '('
> json_value_expr ',' a_expr json_passing_clause_opt
> json_returning_clause_opt
> json_wrapper_behavior
> json_quotes_clause_opt
> json_behavior_clause_opt
> ')'
>
> | JSON_EXISTS '('
> json_value_expr ',' a_expr json_passing_clause_opt
> json_on_error_clause_opt
> ')'
>
> | JSON_VALUE '('
> json_value_expr ',' a_expr json_passing_clause_opt
> json_returning_clause_opt
> json_behavior_clause_opt
> ')'
>
> json_format_clause_opt contains:
> | FORMAT_LA JSON
> {
> $$ = (Node *) makeJsonFormat(JS_FORMAT_JSON, JS_ENC_DEFAULT, @1);
> }
>
>
> That means, all the context_item can specify "FORMAT JSON" options,
> in the meantime, do we need to update these functions
> synopsis/signature in the doc?
>
> some examples:
> create table a(b jsonb);
> create table a1(b int4range);
> select json_value(b format json, 'strict $[*]' DEFAULT 9 ON ERROR) from a;
> select json_value(b format json, 'strict $[*]' DEFAULT 9 ON ERROR) from a1;
> select json_value(text '"1"' format json, 'strict $[*]' DEFAULT 9 ON ERROR);
>
> ------------------------------------------------
> transformJsonValueExpr
>
> /* Try to coerce to the target type. */
> coerced = coerce_to_target_type(pstate, expr, exprtype,
> targettype, -1,
> COERCION_EXPLICIT,
> COERCE_EXPLICIT_CAST,
> location);
>
> based on the function transformJsonValueExpr and subfunction
> coerce_to_target_type,
> for SQL/JSON query functions (JSON_EXISTS, JSON_QUERY, and JSON_VALUE)
> the context_item requirement is any data type that not error out while
> explicitly casting to jsonb in coerce_to_target_type.
>
> I played around with it, I think these types can be used in context_item.
> {char,text,bpchar,character varying } and these types of associated domains.
> bytea data type too, but need specify "ENCODING UTF8".
> e.g.
> select json_value(bytea '"1"' format json ENCODING UTF8, 'strict $[*]'
> DEFAULT 9 ON ERROR);
>
>
> Maybe we can add some brief explanation in this para to explain more
> about "context_item"
> {
> SQL/JSON functions JSON_EXISTS(), JSON_QUERY(), and JSON_VALUE()
> described in Table 9.52 can be used to query JSON documents. Each of
> these functions apply a path_expression (the query) to a context_item
> (the document); see Section 9.16.2 for more details on what
> path_expression can contain.
> }

If I understand correctly, you're suggesting that we add a line to the
above paragraph to mention which types are appropriate for
context_item.  How about we add the following:

<replaceable>context_item</replaceable> expression can be a value of
any type that can be cast to <type>jsonb</type>. This includes types
such as <type>char</type>,  <type>text</type>, <type>bpchar</type>,
<type>character varying</type>, and <type>bytea</type> (with
<code>ENCODING UTF8</code>), as well as any domains over these types.

--
Thanks, Amit Langote