On Mon, Mar 11, 2024 at 11:30 AM jian he <jian.universal...@gmail.com> wrote: > > On Sun, Mar 10, 2024 at 10:57 PM jian he <jian.universal...@gmail.com> wrote: > > > > one more issue. > > Hi > one more documentation issue. > after applied V42, 0001 to 0003, > there are 11 appearance of `FORMAT JSON` in functions-json.html > still not a single place explained what it is for. > > json_query ( context_item, path_expression [ PASSING { value AS > varname } [, ...]] [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 > ] ] ] [ { WITHOUT | WITH { CONDITIONAL | [UNCONDITIONAL] } } [ ARRAY ] > WRAPPER ] [ { KEEP | OMIT } QUOTES [ ON SCALAR STRING ] ] [ { ERROR | > NULL | EMPTY { [ ARRAY ] | OBJECT } | DEFAULT expression } ON EMPTY ] > [ { ERROR | NULL | EMPTY { [ ARRAY ] | OBJECT } | DEFAULT expression } > ON ERROR ]) > > FORMAT JSON seems just a syntax sugar or for compatibility in json_query. > but it returns an error when the returning type category is not > TYPCATEGORY_STRING. > > for example, even the following will return an error. > ` > CREATE TYPE regtest_comptype AS (b text); > SELECT JSON_QUERY(jsonb '{"a":{"b":"c"}}', '$.a' RETURNING > regtest_comptype format json); > ` > > seems only types in[0] will not generate an error, when specifying > FORMAT JSON in JSON_QUERY. > > so it actually does something, not a syntax sugar? >
SELECT * FROM JSON_TABLE(jsonb'[{"aaa": 123}]', 'lax $[*]' COLUMNS (js2 text format json PATH '$' omit quotes)); SELECT * FROM JSON_TABLE(jsonb'[{"aaa": 123}]', 'lax $[*]' COLUMNS (js2 text format json PATH '$' keep quotes)); SELECT * FROM JSON_TABLE(jsonb'[{"aaa": 123}]', 'lax $[*]' COLUMNS (js2 text PATH '$' keep quotes)); -- JSON_QUERY_OP SELECT * FROM JSON_TABLE(jsonb'[{"aaa": 123}]', 'lax $[*]' COLUMNS (js2 text PATH '$' omit quotes)); -- JSON_QUERY_OP SELECT * FROM JSON_TABLE(jsonb'[{"aaa": 123}]', 'lax $[*]' COLUMNS (js2 text PATH '$')); -- JSON_VALUE_OP SELECT * FROM JSON_TABLE(jsonb'[{"aaa": 123}]', 'lax $[*]' COLUMNS (js2 json PATH '$')); -- JSON_QUERY_OP comparing these queries, I think 'FORMAT JSON' main usage is in json_table. CREATE TYPE regtest_comptype AS (b text); SELECT JSON_QUERY(jsonb '{"a":{"b":"c"}}', '$.a' RETURNING regtest_comptype format json); ERROR: cannot use JSON format with non-string output types LINE 1: ..."a":{"b":"c"}}', '$.a' RETURNING regtest_comptype format jso... ^ the error message is not good, but that's a minor issue. we can pursue it later. ----------------------------------------------------------------------------------------- SELECT JSON_QUERY(jsonb 'true', '$' RETURNING int KEEP QUOTES ); SELECT JSON_QUERY(jsonb 'true', '$' RETURNING int omit QUOTES ); SELECT JSON_VALUE(jsonb 'true', '$' RETURNING int); the third query returns integer 1, not sure this is the desired behavior. it obviously has an implication for json_table. ----------------------------------------------------------------------------------------- in jsonb_get_element, we have something like: if (jbvp->type == jbvBinary) { container = jbvp->val.binary.data; have_object = JsonContainerIsObject(container); have_array = JsonContainerIsArray(container); Assert(!JsonContainerIsScalar(container)); } + res = JsonValueListHead(&found); + if (res->type == jbvBinary && JsonContainerIsScalar(res->val.binary.data)) + JsonbExtractScalar(res->val.binary.data, res); So in JsonPathValue, the above (res->type == jbvBinary) is unreachable? also see the comment in jbvBinary. maybe we can just simply do: if (res->type == jbvBinary) Assert(!JsonContainerIsScalar(res->val.binary.data)); ----------------------------------------------------------------------------------------- +<synopsis> +JSON_TABLE ( + <replaceable>context_item</replaceable>, <replaceable>path_expression</replaceable> <optional> AS <replaceable>json_path_name</replaceable> </optional> <optional> PASSING { <replaceable>value</replaceable> AS <replaceable>varname</replaceable> } <optional>, ...</optional> </optional> + COLUMNS ( <replaceable class="parameter">json_table_column</replaceable> <optional>, ...</optional> ) + <optional> { <literal>ERROR</literal> | <literal>EMPTY</literal> } <literal>ON ERROR</literal> </optional> + <optional> + PLAN ( <replaceable class="parameter">json_table_plan</replaceable> ) | + PLAN DEFAULT ( { INNER | OUTER } <optional> , { CROSS | UNION } </optional> + | { CROSS | UNION } <optional> , { INNER | OUTER } </optional> ) + </optional> +) based on the synopsis the following query should not be allowed? SELECT *FROM (VALUES ('"11"'), ('"err"')) vals(js) LEFT OUTER JOIN JSON_TABLE(vals.js::jsonb, '$' COLUMNS (a int PATH '$') default '11' ON ERROR) jt ON true; aslo the synopsis need to reflect case like: SELECT *FROM (VALUES ('"11"'), ('"err"')) vals(js) LEFT OUTER JOIN JSON_TABLE(vals.js::jsonb, '$' COLUMNS (a int PATH '$') NULL ON ERROR) jt ON true;