Converting a mysql dict column using JSON_Table

Question

Edited

I have a mysql table "prop" with a column "detail" that contains a dict field.

fnum    details
 55     '{"a":"3"},{"b":"2"},{"d":"1"}'

I have tried to convert this to a table. using this

SELECT p.fnum, deets.*
FROM prop p
JOIN JSON_TABLE( p.details,
     '$[*]'
     COLUMNS (
              idx FOR ORDINALITY,
              a varChar(10) PATH '$.a',
              b varchar(20) PATH '$.b'
              d varchar(45) PATH '$.d',
              )
     ) deets

I have tried various paths including $.*. I am expecting the following:

fnum   a   b   d
  55    3   2   1

also if I have 2 rows such as

fnum     details
  55     '{"a":"3"},{"b":"2"},{"d":"1"}'
  56     '{"c":"car"}'

should generate the following

fnum    a       b      d       c
  55    3       2      1       null
  56    null    null   null    car

Bill Karwin · Accepted Answer · 2023-01-14 16:42:41Z

1

Your details data is not valid JSON, because it doesn't have [ ] delimiting the array.

Demo:

mysql> create table prop (fnum int, details json);

mysql> insert into prop select 55, '{"a":"3"},{"b":"2"},{"d":"1"}';
ERROR 3140 (22032): Invalid JSON text: "The document root must 
not be followed by other values." at position 9 in value for column
'prop.details'.

mysql> insert into prop select 55, '[{"a":"3"},{"b":"2"},{"d":"1"}]';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

It's worth using the JSON data type instead of storing JSON in a text column, because the JSON data type ensures that the document is valid JSON format. It must be valid JSON to use the JSON_TABLE() function or any other JSON function.

Also your query has some syntax mistakes with respect to commas:

SELECT p.fnum, deets.*
FROM prop p
JOIN JSON_TABLE( p.details,
     '$[*]'
     COLUMNS (
              idx FOR ORDINALITY,
              a varChar(10) PATH '$.a',
              b varchar(20) PATH '$.b'   <-- missing comma
              d varchar(45) PATH '$.d',  <-- extra comma
              )
     ) deets

answered Jan 14, 2023 at 16:42

Bill Karwin

567k87 gold badges710 silver badges871 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

QuentinJS Over a year ago

Thanks for the information. and the typo was due to me mocking it up at midnight. I wil move to the JSON field type

Collectives™ on Stack Overflow

Converting a mysql dict column using JSON_Table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related