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>, Tomas Vondra <tomas.vondra@enterprisedb.com>, Himanshu Upadhyaya <upadhyaya.himanshu@gmail.com>, Erik Rijkers <er@xs4all.nl>, Andres Freund <andres@anarazel.de>, Andrew Dunstan <andrew@dunslane.net>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2024-04-03T03:30:08Z
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.

On Tue, Apr 2, 2024 at 9:57 PM Amit Langote <amitlangote09@gmail.com> wrote:
>
> Please let me know if you have further comments on 0001.  I'd like to
> get that in before spending more energy on 0002.
>

hi. some issues with the doc.
i think, some of the "path expression" can be replaced by
"<replaceable>path_expression</replaceable>".
maybe not all of them.

+  <variablelist>
+   <varlistentry>
+    <term>
+     <literal><replaceable>context_item</replaceable>,
<replaceable>path_expression</replaceable> <optional>
<literal>AS</literal> <replaceable>json_path_name</replaceable>
</optional> <optional> <literal>PASSING</literal> {
<replaceable>value</replaceable> <literal>AS</literal>
<replaceable>varname</replaceable> } <optional>,
...</optional></optional></literal>
+    </term>
+    <listitem>
+    <para>
+     The input data to query, the JSON path expression defining the query,
+     and an optional <literal>PASSING</literal> clause, which can provide data
+     values to the <replaceable>path_expression</replaceable>.
+     The result of the input data
+     evaluation is called the <firstterm>row pattern</firstterm>. The row
+     pattern is used as the source for row values in the constructed view.
+    </para>
+    </listitem>
+   </varlistentry>

maybe
change this part "The input data to query, the JSON path expression
defining the query,"
to
`
<replaceable>context_item</replaceable> is the input data to query,
<replaceable>path_expression</replaceable> is the JSON path expression
defining the query,
`

+    <para>
+     Specifying <literal>FORMAT JSON</literal> makes it explcit that you
+     expect that the value to be a valid <type>json</type> object.
+    </para>
"explcit" change to "explicit", or should it be "explicitly"?
also FORMAT JSON can be override by OMIT QUOTES.
SELECT sub.* FROM JSON_TABLE('{"a":{"z1": "a"}}', '$.a' COLUMNS(xx
TEXT format json path '$.z1' omit quotes))sub;
it return not double quoted literal 'a', which cannot be a valid json.

create or replace FUNCTION test_format_json() returns table (thetype
text, is_ok bool) AS $$
declare
    part1_sql text := $sql$SELECT sub.* FROM JSON_TABLE('{"a":{"z1":
"a"}}', '$.a'  COLUMNS(xx $sql$;
    part2_sql text := $sql$ format json path '$.z1' omit quotes))sub $sql$;
    run_status bool := true;
    r record;
    fin record;
BEGIN
    for r in
        select format_type(oid, -1) as aa
        from pg_type where  typtype = 'b' and typarray != 0 and
typnamespace = 11 and typnotnull is false
    loop
        begin
            -- raise notice '%',CONCAT_WS(' ', part1_sql, r.aa, part2_sql);
            -- raise notice 'r.aa %', r.aa;
            run_status := true;
            execute CONCAT_WS(' ', part1_sql, r.aa, part2_sql) into fin;
            return query select r.aa, run_status;
                exception when others then
                begin
                    run_status := false;
                    return query select r.aa, run_status;
                end;
        end;
    end loop;
END;
$$ language plpgsql;
create table sss_1 as select * from test_format_json();
select * from sss_1 where is_ok is true;

use the above query, I've figure out that FORMAT JSON can apply to the
following types:
bytea
name
text
json
bpchar
character varying
jsonb
and these type's customized domain type.

overall, the idea is that:
     Specifying <literal>FORMAT JSON</literal> makes it explicitly that you
     expect that the value to be a valid <type>json</type> object.
    <literal>FORMAT JSON</literal> can be overridden by OMIT QUOTES
specification, which can make the return value not a valid
<type>json</type>.
    <literal>FORMAT JSON</literal> can only work with certain kinds of
data types.
-----------------------------------------------------------------------------------------------
+    <para>
+     Optionally, you can add <literal>ON ERROR</literal> clause to define
+     error behavior.
+    </para>
I think "error behavior" may refer to "what kind of error message it will omit"
but here, it's about what to do when an error happens.
so I guess it's misleading.

maybe we can explain it similar to json_exist.
+    <para>
+     Optionally, you can add <literal>ON ERROR</literal> clause to define
+      the behavior if an error occurs.
+    </para>

+    <para>
+     The specified <parameter>type</parameter> should have a cast from the
+     <type>boolean</type>.
+    </para>
should be
+    <para>
+     The specified <replaceable>type</replaceable> should have a cast from the
+     <type>boolean</type>.
+    </para>


+    <para>
+     Inserts a SQL/JSON value into the output row.
+    </para>
maybe
+    <para>
+     Inserts a value that the data type is
<replaceable>type</replaceable> into the output row.
+    </para>

+    <para>
+     Inserts a boolean item into each output row.
+    </para>
maybe changed to:
+    <para>
+     Inserts a value that the data type is
<replaceable>type</replaceable> into the output row.
+    </para>

"name type EXISTS" branch mentioned: "The specified type should have a
cast from the boolean."
but "name type [FORMAT JSON [ENCODING UTF8]] [ PATH path_expression ]"
never mentioned the "type"parameter.
maybe add one para, something like:
"after apply path_expression, the yield value cannot be coerce to
<replaceable>type</replaceable> it will return null"