1

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

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Prashant Marathay
  • 185
  • 2
  • 8

1 Answers1

2

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
Allan Wind
  • 23,068
  • 5
  • 28
  • 38
  • 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! – Prashant Marathay Aug 11 '22 at 15:41