Using JSON_TABLE to convert List into Rows

Question

I have a database column (named "product_parents") formatted as a JSON object that contains the following data:

'["A", "B", "G", "H", "C", "E", "P", "R"]'

I want to use JSON_Table to create separate rows for each item.

Ideally I would get something like:

|product_parent|

|A| |B| |C| |...| |P| |R|

I've tried

SELECT *
    FROM pmctb.products AS p,
    JSON_TABLE (p.product_parents, '$[*]'
        COLUMNS (
                pp_id FOR ORDINALITY,
                pp_pn VARCHAR(255) PATH '$.header')
                ) AS pp
    WHERE product_uid = "310-000574"

($.header was just an attempt since there is no column header) but that just returns the table and the ordinality and gives me nulls for pp_pn.

Any help would be appreciated. Thx

user9706 · Accepted Answer · 2022-08-11 05:03:09Z

3

It looks like this does the trick with MySQL 8+:

create table products (product_parents json);

insert into products values ('["A", "B", "G", "H", "C", "E", "P", "R"]');

select pp
from products,
JSON_TABLE(
  products.product_parents,
  '$[*]' columns (pp varchar(255) path '$')
) t;

and the result is:

pp
A
B
G
H
C
E
P
R

answered Aug 11, 2022 at 5:03

user9706

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

1 Comment

Prashant Marathay Over a year ago

Awesome!! Thanks for taking the time to help. It looks like all I needed to do was change the '$.header' to '$' ! I tired "$." but that didn't work! Could not have done it without your help!

Collectives™ on Stack Overflow

Using JSON_TABLE to convert List into Rows

|product_parent|

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

|product_parent|

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related