Re: SQL/JSON: JSON_TABLE
Pavel Stehule <[email protected]> Tue, 14 Jan 2020 20:55:37 +0100
| Newsgroups | gmane.comp.db.postgresql.devel.general |
|---|---|
| Message-ID | <CAFj8pRBaSA06w9WzG52Qu7r10MT2Qxd9Pzg0bbiZCCS_QKhgkg@mail.gmail.com> |
--000000000000fc57f2059c1efbc3
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Hi
I read this patch
There are some typo in doc
*name* *type* EXISTS [ PATH *json_path_specification* ]
*Gerenates* a column and inserts a boolean item into each row of this
column.
Is good to allow repeat examples from documentation - so documentation
should to contains a INSERT with JSON, query and result.
JSON_TABLE is pretty complex function, probably the most complex function
what I know, so I propose to divide documentation to two parts - basic
advanced. The basic should to coverage the work with missing or error
values (with examples), and explain what are wrappers. Advanced part should
to describe work with plans. I afraid so lot of smaller examples has to be
necessary. Personally I propose postpone 0003 and 0004 patches to some next
releases. This is extra functionality and not well used and documented in
other RDBMS (depends on your capacity) - there is problem only in well
documentation - because this feature is not almost used in projects, the
small differences from standard or other RDBMS can be fixed later (like we
fixed XMLTABLE last year).
The documentation is good enough for initial commit - but should be
significantly enhanced before release.
I did some small performance tests - and parsing json with result cca 25000
rows needs 150 ms. It is great time.
My previous objections was solved.
The patches was applied cleanly. The compilation is without any issues and
warnings.
There are enough regress tests, and check-world was passed without problem.
Source code is readable, and well formatted.
I checked standard and checked conformance with other RDBMS.
I will mark this patch - JSON_TABLE implementation as ready for commiter.
The documentation should be enhanced - more examples, more simple examples
are necessary.
Regards
Thank you for your great, complex and hard work
It will be great feature
Pavel
=C3=BAt 14. 1. 2020 v 16:26 odes=C3=ADlatel Nikita Glukhov <n.gluhov@postgr=
espro.ru>
napsal:
> Attached 42th version of the patches rebased onto current master.
>
>
> Changes from the previous version:
> * added EXISTS PATH columns
> * added DEFAULT clause for FORMAT JSON columns
> * added implicit FORMAT JSON for columns of json[b], array and composite=
types
>
>
> On 21.11.2019 19:51, Pavel Stehule wrote:
>
>
> =C4=8Dt 21. 11. 2019 v 17:31 odes=C3=ADlatel Nikita Glukhov <n.gluhov@pos=
tgrespro.ru>
> napsal:
>
>> On 17.11.2019 13:35, Pavel Stehule wrote:
>> I found:
>>
>> a) Oracle & MySQL (Oracle) supports EXISTS clause, this implementation n=
ot.
>> I think should be useful support this clause too.
>>
>> SELECT * FROM JSON_TABLE('...', '...' COLUMNS x INT EXISTS PATH ...
>>
>>
>> EXISTS PATH clause can be emulated with jsonpath EXISTS() predicate:
>>
>> =3D# SELECT *
>> FROM JSON_TABLE('{"a": 1}', '$'
>> COLUMNS (
>> a bool PATH 'exists($.a)',
>> b bool PATH 'exists($.b)'
>> ));
>> a | b
>> ---+---
>> t | f
>> (1 row)
>>
>> But this works as expected only in lax mode. In strict mode EXISTS() re=
turns
>> Unknown that transformed into SQL NULL:
>>
>> =3D# SELECT *
>> FROM JSON_TABLE('{"a": 1}', '$'
>> COLUMNS (
>> a bool PATH 'strict exists($.a)',
>> b bool PATH 'strict exists($.b)'
>> ));
>> a | b
>> ---+---
>> t |
>> (1 row)
>>
>> There is no easy way to return false without external COALESCE(),
>> DEFAULT false ON ERROR also does not help.
>>
>> So, I think it's worth to add EXISTS PATH clause to our implementation.
>>
>>
>> There is a question how to map boolean result to other data types.
>>
>> Now, boolean result can be used in JSON_TABLE columns of bool, int4, tex=
t,
>> json[b], and other types which have CAST from bool:
>>
>> SELECT *
>> FROM JSON_TABLE('{"a": 1}', '$'
>> COLUMNS (
>> a int PATH 'exists($.a)',
>> b text PATH 'exists($.b)'
>> ));
>> a | b
>> ---+-------
>> 1 | false
>> (1 row)
>>
>> EXISTS PATH columns were added. Only column types having CASTS
> from boolean type are accepted.
>
> Example:
>
> SELECT *
> FROM JSON_TABLE(
> '{"foo": "bar"}', '$'
> COLUMNS (
> foo_exists boolean EXISTS PATH '$.foo',
> foo int EXISTS,
> err text EXISTS PATH '$ / 0' TRUE ON ERROR
> )
> );
>
> foo_exists | foo | err
> ------------+-----+------
> t | 1 | true
> (1 row)
>
>
>
> b) When searched value is not scalar, then it returns null. This behave c=
an be
>> suppressed by clause FORMAT Json. I found a different behave, and maybe =
I found
>> a bug. On MySQL this clause is by default for JSON values (what has sen=
se).
>>
>> SELECT *
>> FROM
>> JSON_TABLE(
>> '[{"a":[1,2]}]',
>> '$[*]'
>> COLUMNS(
>> aj JSON PATH '$.a' DEFAULT '{"x": 333}' ON EMPTY
>> )
>> ) AS tt;
>>
>> It returns null, although it should to return [1,2].
>>
>> Yes, regular (non-formatted) JSON_TABLE columns can accept only scalar v=
alues.
>> Otherwise an error is thrown, which can be caught by ON ERROR clause. Th=
is
>> behavior is specified by the standard.
>>
>> FORMAT JSON is not implicitly added for json[b] columns now. The current=
SQL
>> standard does not have any json data types, so I think we can add implic=
it
>> FORMAT JSON for json[b] typed-columns. But I'm a bit afraid that differ=
ent
>> behavior can be standardized after introduction of json data types in SQ=
L.
>>
>>
>> There is another bug maybe. Although there is DEFAULT clause. It returns=
NULL.
>>
>> ON ERROR should be used if "not a scalar" error needs to be caught:
>>
>> SELECT *
>> FROM
>> JSON_TABLE(
>> '[{"a":[1,2]}]',
>> '$[*]'
>> COLUMNS(
>> aj JSON PATH '$.a' DEFAULT '{"x": 333}' ON ERROR
>> )
>> ) AS tt;
>>
>> aj
>> ------------
>> {"x": 333}
>> (1 row)
>>
>>
>> ON EMPTY catches only empty-result case (for example, non-existent path =
in
>> lax mode):
>>
>> SELECT *
>> FROM
>> JSON_TABLE(
>> '[{"a":[1,2]}]',
>> '$[*]'
>> COLUMNS(
>> aj JSON PATH '$.foo' DEFAULT '{"x": 333}' ON EMPTY
>> )
>> ) AS tt;
>> aj
>> ------------
>> {"x": 333}
>> (1 row)
>>
>>
>> I got correct result when I used FORMAT JSON clause.
>> I think it should be default behave for json and jsonb columns.
>>
>> I agree that FORMAT JSON could be implicit for json[b] columns. But I t=
hink
>> there could be one minor problem if we want to verify that returned valu=
e is
>> scalar.
>>
>> Without FORMAT JSON this is verified by the underlying JSON_VALUE expres=
sion:
>>
>> SELECT *
>> FROM
>> JSON_TABLE(
>> '[{"a":[1,2]}]',
>> '$[*]'
>> COLUMNS (
>> aj JSON PATH 'lax $.a' ERROR ON ERROR
>> )
>> ) AS tt;
>> ERROR: JSON path expression in JSON_VALUE should return singleton scala=
r item
>>
>> (This error message with the reference to implicit JSON_VALUE needs to b=
e fixed.)
>>
>>
>> But with FORMAT JSON we need to construct complex jsonpath with a filter=
and
>> override ON EMPTY behavior:
>>
>> SELECT *
>> FROM
>> JSON_TABLE(
>> '[{"a":[1,2]}]',
>> '$[*]'
>> COLUMNS (
>> aj JSON FORMAT JSON
>> -- strict mode is mandatory to prevent array unwrapping
>> PATH 'strict $.a ? (@.type() !=3D "array" && @.type() !=3D "=
object")'
>> ERROR ON EMPTY ERROR ON ERROR
>> )
>> ) AS tt;
>> ERROR: no SQL/JSON item
>>
>> please, check the behave of other databases. I think so good conformance
> with other RDBMS is important. More this method for checking if value is
> object or not looks little bit scary.
>
> maybe we can implement some functions like JSON_IS_OBJECT(),
> JSON_IS_ARRAY(), JSON_IS_VALUE()?
>
> More - we have this functionality already
>
> ostgres=3D# select json_typeof('[10,20]');
> =E2=94=8C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=90
> =E2=94=82 json_typeof =E2=94=82
> =E2=95=9E=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=
=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=A1
> =E2=94=82 array =E2=94=82
> =E2=94=94=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=98
> (1 row)
>
> Implicit FORMAT JSON is used for columns of json[b], array and composite =
types now.
> The behavior is similar to behavior of json_populate_record().
>
> Example:
>
> CREATE TYPE test_record AS (foo text[], bar int);
>
> SELECT *
> FROM JSON_TABLE(
> '{"foo": ["bar", 123, null]}', '$'
> COLUMNS (
> js json PATH '$',
> jsonb_arr jsonb[] PATH '$.foo',
> text_arr text[] PATH '$.foo',
> int_arr int[] PATH '$.foo' DEFAULT '{}' ON ERROR,
> rec test_record PATH '$'
> )
> );
> js | jsonb_arr | text_arr | in=
t_arr | rec
> -----------------------------+----------------------+----------------+---=
------+---------------------
> {"foo": ["bar", 123, null]} | {"\"bar\"",123,NULL} | {bar,123,NULL} | {}=
| ("{bar,123,NULL}",)
> (1 row)
>
>
> Another question - when I used FORMAT JSON clause, then I got syntax erro=
r
>> on DEFAULT keyword .. . Is it correct?
>>
>> Why I cannot to use together FORMAT JSON and DEFAULT clauses?
>>
>> JSON_TABLE columns with FORMAT JSON, like JSON_QUERY, can have only
>> ERROR, NULL, EMPTY ARRAY, EMPTY OBJECT behaviors.
>>
>> This syntax is specified in the SQL standard:
>>
>> <JSON table formatted column definition> ::=3D
>> <column name> <data type> FORMAT <JSON representation>
>> [ PATH <JSON table column path specification> ]
>> [ <JSON table formatted column wrapper behavior> WRAPPER ]
>> [ <JSON table formatted column quotes behavior> QUOTES [ ON SCALAR STR=
ING ] ]
>> [ <JSON table formatted column empty behavior> ON EMPTY ]
>> [ <JSON table formatted column error behavior> ON ERROR ]
>>
>> <JSON table formatted column empty behavior> ::=3D
>> ERROR
>> | NULL
>> | EMPTY ARRAY
>> | EMPTY OBJECT
>>
>> <JSON table formatted column error behavior> ::=3D
>> ERROR
>> | NULL
>> | EMPTY ARRAY
>> | EMPTY OBJECT
>>
>>
>> But I also think that DEFAULT clause could be very useful in JSON_QUERY =
and
>> formatted JSON_TABLE columns.
>>
>> DEFAULT clause was enabled in JSON_QUERY() and formatted JSON_TABLE colu=
mns:
>
> SELECT *
> FROM JSON_TABLE(
> '{"foo": "bar"}', '$'
> COLUMNS (
> baz json FORMAT JSON DEFAULT '"empty"' ON EMPTY
> )
> );
> baz
> ---------
> "empty"
> (1 row)
>
>
>
> --
> Nikita Glukhov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
--000000000000fc57f2059c1efbc3
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
<div dir=3D"ltr"><div>Hi</div><div><br></div><div>I read this patch <br></d=
iv><div><br></div><div>There are some typo in doc</div><div><br></div><div>=
<dt><span class=3D"gmail-term">
<code class=3D"gmail-literal">
<em class=3D"gmail-replaceable"><code>name</code></em> <em class=3D"=
gmail-replaceable"><code>type</code></em>
EXISTS [ PATH <em class=3D"gmail-replaceable"><code>json_path_specif=
ication</code></em> ]
</code>
</span></dt><dd><p>
<b>Gerenates</b> a column and inserts a boolean item into each row of =
this column.
</p></dd></div><div><br></div><div>Is good to allow repeat examples fro=
m documentation - so documentation should to contains a INSERT with JSON, q=
uery and result. <br></div><div><br></div><div>JSON_TABLE is pretty complex=
function, probably the most complex function what I know, so I propose to =
divide documentation to two parts - basic advanced. The basic should to cov=
erage the work with missing or error values (with examples), and explain wh=
at are wrappers. Advanced part should to describe work with plans. I afraid=
so lot of smaller examples has to be necessary. Personally I propose postp=
one 0003 and 0004 patches to some next releases. This is extra functionalit=
y and not well used and documented in other RDBMS (depends on your capacity=
) - there is problem only in well documentation - because this feature is n=
ot almost used in projects, the small differences from standard or other RD=
BMS can be fixed later (like we fixed XMLTABLE last year).</div><div><br></=
div><div>The documentation is good enough for initial commit - but should b=
e significantly enhanced before release. <br></div><div><br></div><div>I di=
d some small performance tests - and parsing json with result cca 25000 row=
s needs 150 ms. It is great time. <br></div><div><br></div><div>My previous=
objections was solved. <br></div><div><br></div><div>The patches was appli=
ed cleanly. The compilation is without any issues and warnings.</div><div>T=
here are enough regress tests, and check-world was passed without problem.<=
/div><div>Source code is readable, and well formatted.<br></div><div><br></=
div><div>I checked standard and checked conformance with other RDBMS. <br><=
/div><div><br></div><div>I will mark this patch - JSON_TABLE implementation=
as ready for commiter. The documentation should be enhanced - more example=
s, more simple examples are necessary.</div><div><br></div><div>Regards</di=
v><div><br></div><div>Thank you for your great, complex and hard work</div>=
<div><br></div><div>It will be great feature</div><div><br></div><div>Pavel=
<br></div><div><br></div><div><br></div><div><br></div><div><br></div><div>=
<br></div></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gm=
ail_attr">=C3=BAt 14. 1. 2020 v=C2=A016:26 odes=C3=ADlatel Nikita Glukhov &=
lt;<a href=3D"mailto:[email protected]">[email protected]</a>&g=
t; napsal:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0=
px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
=20
=20
=20
<div bgcolor=3D"#FFFFFF">
<pre>Attached 42th version of the patches rebased onto current master.
Changes from the previous version:
* added EXISTS PATH columns
* added DEFAULT clause for FORMAT JSON columns
* added implicit FORMAT JSON for columns of json[b], array and composite t=
ypes
</pre>
<p><br>
</p>
<div>On 21.11.2019 19:51, Pavel Stehule
wrote:<br>
</div>
<blockquote type=3D"cite">
=20
<div dir=3D"ltr">
<div dir=3D"ltr"><br>
</div>
=C4=8Dt 21. 11. 2019 v=C2=A017:31 odes=C3=ADlatel Nikita Glukhov &l=
t;<a href=3D"mailto:[email protected]" target=3D"_blank">n.gluhov@pos=
tgrespro.ru</a>>
napsal:<br>
<div class=3D"gmail_quote">
<blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8=
ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div bgcolor=3D"#FFFFFF">
<p>On 17.11.2019 13:35, Pavel Stehule wrote:<br>
</p>
I found:
<blockquote type=3D"cite">
<div dir=3D"ltr">
<div class=3D"gmail_quote">
<div> </div>
<div> </div>
<div>
<pre>a) Oracle & MySQL (Oracle) supports EXISTS c=
lause, this implementation not.=20
I think should be useful support this clause too. </pre>
</div>
<div>
<pre>SELECT * FROM JSON_TABLE('...', '...=
' COLUMNS x INT EXISTS PATH ...</pre>
</div>
<div><br>
</div>
</div>
</div>
</blockquote>
<pre>EXISTS PATH clause can be emulated with jsonpath EXISTS(=
) predicate:
</pre>
<pre>=3D# SELECT *=20
FROM JSON_TABLE('{"a": 1}', '$'
COLUMNS (
a bool PATH 'exists($.a)',
b bool PATH 'exists($.b)'
));
a | b=20
---+---
t | f
(1 row)
But this works as expected only in lax mode. In strict mode EXISTS() retur=
ns=20
Unknown that transformed into SQL NULL:
=3D# SELECT *=20
FROM JSON_TABLE('{"a": 1}', '$'=20
COLUMNS (
a bool PATH 'strict exists($.a)',
b bool PATH 'strict exists($.b)'
));
a | b=20
---+---
t |=20
(1 row)
There is no easy way to return false without external COALESCE(),
DEFAULT false ON ERROR also does not help. =20
So, I think it's worth to add EXISTS PATH clause to our implementation.
</pre>
</div>
</blockquote>
</div>
</div>
</blockquote>
<pre></pre>
<blockquote type=3D"cite">
<div dir=3D"ltr">
<div class=3D"gmail_quote">
<blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8=
ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div bgcolor=3D"#FFFFFF">
<pre></pre>
<blockquote type=3D"cite">
<div dir=3D"ltr">
<div class=3D"gmail_quote">
<div>
<pre>There is a question how to map boolean result to=
other data types. </pre>
</div>
</div>
</div>
</blockquote>
<pre>Now, boolean result can be used in JSON_TABLE columns of=
bool, int4, text,=20
json[b], and other types which have CAST from bool:
</pre>
<pre>SELECT *=20
FROM JSON_TABLE('{"a": 1}', '$'
COLUMNS (
a int PATH 'exists($.a)',
b text PATH 'exists($.b)'
));
a | b=20
---+-------
1 | false
(1 row)
</pre>
</div>
</blockquote>
</div>
</div>
</blockquote>
<pre>EXISTS PATH columns were added. Only column types having CASTS=20
from boolean type are accepted.
Example:
SELECT *=20
FROM JSON_TABLE(
'{"foo": "bar"}', '$'
COLUMNS (
foo_exists boolean EXISTS PATH '$.foo',
foo int EXISTS,
err text EXISTS PATH '$ / 0' TRUE ON ERROR
)
);
foo_exists | foo | err=20
------------+-----+------
t | 1 | true =20
(1 row)
</pre>
<blockquote type=3D"cite">
<div dir=3D"ltr">
<div class=3D"gmail_quote">
<blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8=
ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div bgcolor=3D"#FFFFFF">
<blockquote type=3D"cite">
<div dir=3D"ltr">
<div class=3D"gmail_quote">
<div>
<pre>b) When searched value is not scalar, then it re=
turns null. This behave can be=20
suppressed by clause FORMAT Json. I found a different behave, and maybe I f=
ound
a bug. On MySQL this clause is by default for JSON values (what has sense)=
.</pre>
</div>
<div>
<pre>SELECT *
FROM
=C2=A0 =C2=A0 =C2=A0 JSON_TABLE(
=C2=A0 =C2=A0 =C2=A0 =C2=A0 '[{"a":[1,2]}]',
=C2=A0 =C2=A0 =C2=A0 =C2=A0 '$[*]'
=C2=A0 =C2=A0 =C2=A0 =C2=A0 COLUMNS(
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0aj JSON PATH '$.a' DEFAULT '{=
"x": 333}' ON EMPTY=20
=C2=A0 =C2=A0 =C2=A0 =C2=A0 )
=C2=A0 =C2=A0 =C2=A0 ) AS tt;</pre>
</div>
<div>
<pre>It returns null, although it should to return [1=
,2]. </pre>
</div>
</div>
</div>
</blockquote>
<pre>Yes, regular (non-formatted) JSON_TABLE columns can acce=
pt only scalar values.=20
Otherwise an error is thrown, which can be caught by ON ERROR clause. This=
=20
behavior is specified by the standard.
FORMAT JSON is not implicitly added for json[b] columns now. The current SQ=
L
standard does not have any json data types, so I think we can add implicit=
=20
FORMAT JSON for json[b] typed-columns. But I'm a bit afraid that diffe=
rent=20
behavior can be standardized after introduction of json data types in SQL.
</pre>
<blockquote type=3D"cite">
<div dir=3D"ltr">
<div class=3D"gmail_quote">
<div>
<pre>There is another bug maybe. Although there is DE=
FAULT clause. It returns NULL.</pre>
</div>
</div>
</div>
</blockquote>
<pre>ON ERROR should be used if "not a scalar" erro=
r needs to be caught:
SELECT *
FROM
JSON_TABLE(
'[{"a":[1,2]}]',
'$[*]'
COLUMNS(
aj JSON PATH '$.a' DEFAULT '{"x": 333}=
9; ON ERROR
)
) AS tt;
aj =20
------------
{"x": 333}
(1 row)
ON EMPTY catches only empty-result case (for example, non-existent path in=
=20
lax mode):
SELECT *
FROM
JSON_TABLE(
'[{"a":[1,2]}]',
'$[*]'
COLUMNS(
aj JSON PATH '$.foo' DEFAULT '{"x": 333}&=
#39; ON EMPTY=20
)
) AS tt;
aj =20
------------
{"x": 333}
(1 row)
</pre>
</div>
</blockquote>
</div>
</div>
</blockquote>
<blockquote type=3D"cite">
<div dir=3D"ltr">
<div class=3D"gmail_quote">
<blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8=
ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div bgcolor=3D"#FFFFFF">
<blockquote type=3D"cite">
<div dir=3D"ltr">
<div class=3D"gmail_quote">
<div>
<pre>I got correct result when I used FORMAT JSON cla=
use.=20
I think it should be default behave for json and jsonb columns.</pre>
</div>
</div>
</div>
</blockquote>
<pre>I agree that FORMAT JSON could be implicit for json[b] c=
olumns. But I think
there could be one minor problem if we want to verify that returned value i=
s=20
scalar.
Without FORMAT JSON this is verified by the underlying JSON_VALUE expressio=
n:
SELECT *
FROM
JSON_TABLE(
'[{"a":[1,2]}]',
'$[*]'
COLUMNS (
aj JSON PATH 'lax $.a' ERROR ON ERROR=20
)
) AS tt;
ERROR: JSON path expression in JSON_VALUE should return singleton scalar i=
tem
(This error message with the reference to implicit JSON_VALUE needs to be f=
ixed.)
But with FORMAT JSON we need to construct complex jsonpath with a filter an=
d=20
override ON EMPTY behavior:
SELECT *
FROM
JSON_TABLE(
'[{"a":[1,2]}]',
'$[*]'
COLUMNS (
aj JSON FORMAT JSON=20
-- strict mode is mandatory to prevent array unwrapping
PATH 'strict $.a ? (@.type() !=3D "array" &&a=
mp; @.type() !=3D "object")'
ERROR ON EMPTY ERROR ON ERROR
)
) AS tt;
ERROR: no SQL/JSON item</pre>
</div>
</blockquote>
<div>please, check the behave of other databases. I think so
good conformance with other RDBMS is important. More this
method for checking if value is object or not looks little
bit scary.</div>
<div><br>
</div>
<div> maybe we can implement some functions like
JSON_IS_OBJECT(), JSON_IS_ARRAY(), JSON_IS_VALUE()?<br>
</div>
<div>=C2=A0</div>
<div>More - we have this functionality already</div>
<div><br>
</div>
<div><span style=3D"font-family:monospace">ostgres=3D# select
json_typeof('[10,20]');<br>
=E2=94=8C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=90=
<br>
=E2=94=82 json_typeof =E2=94=82<br>
=E2=95=9E=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=
=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=A1=
<br>
=E2=94=82 array =C2=A0 =C2=A0 =C2=A0 =E2=94=82<br>
=E2=94=94=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=98=
<br>
(1 row)</span><br>
</div>
</div>
</div>
</blockquote>
<pre>Implicit FORMAT JSON is used for columns of json[b], array and com=
posite types now.
The behavior is similar to behavior of json_populate_record().
Example:
CREATE TYPE test_record AS (foo text[], bar int);
</pre>
<pre>SELECT *=20
FROM JSON_TABLE(
'{"foo": ["bar", 123, null]}', '$'
COLUMNS (
js json PATH '$',=20
jsonb_arr jsonb[] PATH '$.foo',=20
text_arr text[] PATH '$.foo',=20
int_arr int[] PATH '$.foo' DEFAULT '{}' ON ERROR,=20
rec test_record PATH '$'
)
);
js | jsonb_arr | text_arr | int_=
arr | rec =20
-----------------------------+----------------------+----------------+-----=
----+---------------------
{"foo": ["bar", 123, null]} | {"\"bar\"=
",123,NULL} | {bar,123,NULL} | {} | ("{bar,123,NULL}",)
(1 row)
</pre>
<blockquote type=3D"cite">
<div dir=3D"ltr">
<div class=3D"gmail_quote">
<blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8=
ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div bgcolor=3D"#FFFFFF">
<blockquote type=3D"cite">
<div dir=3D"ltr">
<div class=3D"gmail_quote">
<div>
<pre>Another question - when I used FORMAT JSON claus=
e, then I got syntax error
on DEFAULT keyword .. . Is it correct?=20
Why I cannot to use together FORMAT JSON and DEFAULT clauses?</pre>
</div>
</div>
</div>
</blockquote>
<pre>JSON_TABLE columns with FORMAT JSON, like JSON_QUERY, ca=
n have only=20
ERROR, NULL, EMPTY ARRAY, EMPTY OBJECT behaviors.
This syntax is specified in the SQL standard:
<JSON table formatted column definition> ::=3D
<column name> <data type> FORMAT <JSON representation>
[ PATH <JSON table column path specification> ]
[ <JSON table formatted column wrapper behavior> WRAPPER ]
[ <JSON table formatted column quotes behavior> QUOTES [ ON SCALAR =
STRING ] ]
[ <JSON table formatted column empty behavior> ON EMPTY ]
[ <JSON table formatted column error behavior> ON ERROR ]
<JSON table formatted column empty behavior> ::=3D
ERROR
| NULL
| EMPTY ARRAY
| EMPTY OBJECT
<JSON table formatted column error behavior> ::=3D
ERROR
| NULL
| EMPTY ARRAY
| EMPTY OBJECT
But I also think that DEFAULT clause could be very useful in JSON_QUERY and=
=20
formatted JSON_TABLE columns.</pre>
</div>
</blockquote>
</div>
</div>
</blockquote>
<pre>DEFAULT clause was enabled in JSON_QUERY() and formatted JSON_TABL=
E columns:
SELECT *=20
FROM JSON_TABLE(
'{"foo": "bar"}', '$'
COLUMNS (
baz json FORMAT JSON DEFAULT '"empty"' ON EMPTY
)
);
baz =20
---------
"empty"
(1 row)
</pre>
-- <br>
<div>Nikita Glukhov<br>
Postgres Professional: <a href=3D"http://www.postgrespro.com" target=
=3D"_blank">http://www.postgrespro.com</a><br>
The Russian Postgres Company<br>
</div>
</div>
</blockquote></div>
--000000000000fc57f2059c1efbc3--