Re: remaining sql/json patches

Hi,

On Sat, Apr 6, 2024 at 3:55 PM jian he <jian.universality@gmail.com> wrote:
> On Sat, Apr 6, 2024 at 2:03 PM Amit Langote <amitlangote09@gmail.com> wrote:
> >
> > >
> > > * problem with type "char". the view def  output is not the same as
> > > the select * from v1.
> > >
> > > create or replace view v1 as
> > > SELECT col FROM s,
> > > JSON_TABLE(jsonb '{"d": ["hello", "hello1"]}', '$' as c1
> > > COLUMNS(col "char" path '$.d' without wrapper keep quotes))sub;
> > >
> > > \sv v1
> > > CREATE OR REPLACE VIEW public.v1 AS
> > >  SELECT sub.col
> > >    FROM s,
> > >     JSON_TABLE(
> > >             '{"d": ["hello", "hello1"]}'::jsonb, '$' AS c1
> > >             COLUMNS (
> > >                 col "char" PATH '$."d"'
> > >             )
> > >         ) sub
> > > one under the hood called JSON_QUERY_OP, another called JSON_VALUE_OP.
> >
> > Hmm, I don't see a problem as long as both are equivalent or produce
> > the same result.  Though, perhaps we could make
> > get_json_expr_options() also deparse JSW_NONE explicitly into "WITHOUT
> > WRAPPER" instead of a blank.  But that's existing code, so will take
> > care of it as part of the above open item.
> >
> > > I will do extensive checking for other types later, so far, other than
> > > these two issues,
> > > get_json_table_columns is pretty solid, I've tried nested columns with
> > > nested columns, it just works.
> >
> > Thanks for checking.
> >
> After applying v50, this type also has some issues.
> CREATE OR REPLACE VIEW t1 as
> SELECT sub.* FROM JSON_TABLE(jsonb '{"d": ["hello", "hello1"]}',
> '$' AS c1 COLUMNS (
> "tsvector0" tsvector path '$.d' without wrapper omit quotes,
> "tsvector1" tsvector path '$.d' without wrapper keep quotes))sub;
> table t1;
>
> return
>         tsvector0        |        tsvector1
> -------------------------+-------------------------
>  '"hello1"]' '["hello",' | '"hello1"]' '["hello",'
> (1 row)
>
> src5=# \sv t1
> CREATE OR REPLACE VIEW public.t1 AS
>  SELECT tsvector0,
>     tsvector1
>    FROM JSON_TABLE(
>             '{"d": ["hello", "hello1"]}'::jsonb, '$' AS c1
>             COLUMNS (
>                 tsvector0 tsvector PATH '$."d"' OMIT QUOTES,
>                 tsvector1 tsvector PATH '$."d"'
>             )
>         ) sub
>
> but
>
>  SELECT tsvector0,
>     tsvector1
>    FROM JSON_TABLE(
>             '{"d": ["hello", "hello1"]}'::jsonb, '$' AS c1
>             COLUMNS (
>                 tsvector0 tsvector PATH '$."d"' OMIT QUOTES,
>                 tsvector1 tsvector PATH '$."d"'
>             )
>         ) sub
>
> only return
>         tsvector0        | tsvector1
> -------------------------+-----------
>  '"hello1"]' '["hello",' |

Yep, we *should* fix get_json_expr_options() to emit KEEP QUOTES and
WITHOUT WRAPPER options so that transformJsonTableColumns() does the
correct thing when you execute the \sv output.  Like this:

diff --git a/src/backend/utils/adt/ruleutils.c
b/src/backend/utils/adt/ruleutils.c
index 283ca53cb5..5a6aabe100 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -8853,9 +8853,13 @@ get_json_expr_options(JsonExpr *jsexpr,
deparse_context *context,
             appendStringInfo(context->buf, " WITH CONDITIONAL WRAPPER");
         else if (jsexpr->wrapper == JSW_UNCONDITIONAL)
             appendStringInfo(context->buf, " WITH UNCONDITIONAL WRAPPER");
+        else if (jsexpr->wrapper == JSW_NONE)
+            appendStringInfo(context->buf, " WITHOUT WRAPPER");

         if (jsexpr->omit_quotes)
             appendStringInfo(context->buf, " OMIT QUOTES");
+        else
+            appendStringInfo(context->buf, " KEEP QUOTES");
     }

Will get that pushed tomorrow.  Thanks for the test case.

--
Thanks, Amit Langote