Re: remaining sql/json patches

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",' |