[MDEV-25875] JSON_TABLE: extract document fragment into JSON column Created: 2021-06-08  Updated: 2022-08-17  Resolved: 2022-05-31

Status: Closed
Project: MariaDB Server
Component/s: JSON
Fix Version/s: 10.6.9

Type: Task Priority: Blocker
Reporter: Sergei Petrunia Assignee: Rucha Deodhar
Resolution: Fixed Votes: 1
Labels: None

Issue Links:
Blocks
Relates
relates to MDEV-25881 JSON_TABLE can't handle an array prop... Closed

 Description   

Currently, JSON_TABLE doesn't allow one to extract a JSON "subdocument" into a JSON column.

Both the SQL Standard and MySQL-8 provide this capability. This task is about implementing it.

I was sent a patch with MySQL-8 syntax support, so I assume we're going to implement MySQL-8 compatible syntax, which is just to allow specifying column type as JSON. For example:

select * from
 
  json_table('{"foo": [1,2,3,4]}',
 
             '$' columns( jscol json path '$.foo')
 
             ) as T;

should emit [1,2,3,4].



 Comments   
Comment by Sergei Petrunia [ 2021-06-08 ]

First patch:
https://github.com/MariaDB/server/commit/02469bdead5753eccb5d70c98a158a07027f4eb2

Comment by Sergei Petrunia [ 2021-06-08 ]

Review input: https://lists.launchpad.net/maria-developers/msg12761.html

Comment by Sergei Petrunia [ 2021-06-09 ]

For the record, the SQL Standard's way of extracting JSON documents is as
follows:

Columns can be either "regular" (this is what is currently supported), or
"formatted":

<JSON table column definition> ::=
 
    <JSON table ordinality column definition>
 
  | <JSON table regular column definition>
 
  | <JSON table formatted column definition>
 
  | <JSON table nested columns>

Regular columns are just name-datatype:

<JSON table regular column definition> ::=
 
  <column name> <data type>
 
    [ PATH <JSON table column path specification> ]
 
    ...

Formatted columns have more options: 

<JSON table formatted column definition> ::=
 
  <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 ]

one can produce an empty array or empty object in on-error and on-empty conditions, etc.

Comment by Alexey Botchkov [ 2021-06-16 ]

https://github.com/MariaDB/server/commit/fe0dc6ba769dcb468b37a8c3e3c636c0049f7307

Comment by Sergei Petrunia [ 2022-02-18 ]

Review input:

--- a/mysql-test/suite/json/t/json_table.test
 
+++ b/mysql-test/suite/json/t/json_table.test
 
...
 
+#
 
+# MDEV-25875 SON_TABLE: extract document fragment into JSON column.
 
+#

Please fix the typo, SON_TABLE.

--- a/sql/json_table.cc
 
+++ b/sql/json_table.cc
 
 
 
+  if (ctype == PATH)
 
+    m_format_json=
 
+      MY_TEST(m_field->type_handler() == &type_handler_json_longtext);
 
+

Please use { } brackets as the statement inside the if takes multiple lines.
I've had to change the constant to type_handler_long_blob_json when applying the patch.

Ok to push after the above is addressed.

Comment by Rucha Deodhar [ 2022-05-19 ]

Patch for community server: Patch1 and fixup for Patch 1

Comment by Sergei Petrunia [ 2022-05-27 ]

Hi Rucha,

Please change the commit title (of both commits) to mention
MDEV-25875: JSON_TABLE: extract document fragment into JSON column

ok to push after that is done.

Comment by Rucha Deodhar [ 2022-05-31 ]

Pushed to 10.6 community server: a9f6abe and a616035

Generated at Thu Feb 08 09:41:03 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.