MariaDB's JSON_TABLE won't select the whole document with the '$' json expression

user_1284942 0
Viewed 72

Finally I decided to give it a try to this powerful JSON_TABLE function I keep seeing everywhere, and I am having an issue.

The minimum query I found to replicate it is:

SELECT * FROM JSON_TABLE('[{"foo":"bar","bar":5}]','$[*]' columns ( foo text path '$.foo' , bar int path '$.bar' , whole_row_json longtext path '$' )) AS x

Or even wrapped like

SELECT * FROM JSON_TABLE('{"data":[{"foo":"bar","bar":5}]}','$.data[*]' columns ( foo text path '$.foo' , bar int path '$.bar' , whole_row_json longtext path '$' )) AS x;

But for a complex requirement (and debugging purposes) I need the whole json document in a column, but I keep receiving null instead

foo bar whole_row_json
bar 5 null

I read the docs and even this notation is used on another answers here on SO, I have tried another weird notations (like '$.', '$', '$.'), but nothing seems to work.

What am I missing?

MariaDB Version: 10.7.4

Edits: What I need is the the json document that generated each row, not the literal whole json input

This issue seems to be reported on https://jira.mariadb.org/browse/MDEV-25875 and according to my findings, this doesn't work either on bare metal mariadb 10.7.4 nor docker mariadb:10.6.8-focal, mariadb:10.8, mariadb:10.9-rc

mariadb mysql
2 Answers

Try

SELECT * 
  FROM JSON_TABLE('[{"foo":"bar","bar":5}]',
       '$[*]' columns (
           foo text path '$.foo',
           bar int path '$.bar',
           whole_json JSON path '$'
       )) AS x;

I only changed the datatype from longtext to json.

Update

This won't work on MariaDB 10.7.4.

user_196571830

I recall the path you name after path must be relative to the sub-object that json_table() is processing. You can't use that path to reference the top-level JSON document.

https://mariadb.com/docs/reference/mdb/functions/JSON_TABLE/ says:

Regular columns require a JSON Path expression for the column, which is used to search for the column within the current SQL/JSON item produced by the JSON Path expression for the row.

(emphasis mine)

But you could create the document outside the json_table() function and then reference it in your select-list:

select x.*, j.myjson as whole_json 
from (select '[{"foo":"bar","bar":5}]' as myjson) as j
cross join json_table(j.myjson,
       '$[*]' columns (
           foo text path '$.foo',
           bar int path '$.bar'
       )) AS x;
foo bar whole_json
bar 5 [{"foo":"bar","bar":5}]

https://dbfiddle.uk/?rdbms=mariadb_10.6&fiddle=b42384efd007b2e09193397b92328c0c

Bill_Karwin_208600
Related