Using JSON_TABLE to convert an ARRAY into Columns

Question

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

'{"300-000012": {"is_complete": "False", "is_in_progress": "True"}, 
  "300-000018": {"is_complete": "True", "is_in_progress": "False"}}'

I can't seem to convert the Array into Columns. I've tried

SELECT mh.*, jt.*
FROM history AS mh,
JSON_TABLE (mh.details, '$[*]' 
    COLUMNS (
        NESTED PATH '$.*' COLUMNS (jt_complete VARCHAR(255) PATH '$.is_complete'),
        NESTED PATH '$.*' COLUMNS (jt_progress VARCHAR(255) PATH '$.is_in_progress')
        )
        ) AS jt)

But I get an Error Code

Error Code: 3143. Invalid JSON path expression

Ideally I would get something like:

  details             jt_complete            jt_progress
  300-000012             FALSE                  TRUE
  300-000018              TRUE                  FALSE

Any help would be appreciated. Thx

Out of curiosity, why are you storing the data in JSON if you want them in individual columns? — Bill Karwin
– Bill Karwin, Commented Nov 20, 2020 at 0:32
We store all the information in a JSON object so it is in 1 row but then we need to break out the JSON object when we want to utilize the data. LMK if that makes sense. — Prashant Marathay
– Prashant Marathay, Commented Nov 27, 2020 at 4:18

Nick · Accepted Answer · 2020-11-20 00:31:14Z

1

This is a tricky one because the keys of the object are variable. This means you need to extract the keys and the values separately for each object. The values can be connected by using an ordinality column for each JSON_TABLE and joining them on that:

SELECT mh.id, jk.details, jt.jt_complete, jt.jt_progress
FROM history mh
JOIN JSON_TABLE(
  JSON_KEYS(mh.details),
  '$[*]' COLUMNS (
    rn FOR ORDINALITY,
    details VARCHAR(10) PATH '$'
  )
) jk
JOIN JSON_TABLE(
  JSON_EXTRACT(mh.details, '$.*'),
  '$[*]' COLUMNS (
    rn FOR ORDINALITY,
    jt_complete VARCHAR(10) PATH '$.is_complete',
    jt_progress VARCHAR(10) PATH '$.is_in_progress'
  )
) jt ON jt.rn = jk.rn

Demo on dbfiddle

answered Nov 20, 2020 at 0:31

Nick

147k23 gold badges67 silver badges106 bronze badges

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

2 Comments

Prashant Marathay Over a year ago

As a follow-on to this question - is it possible to return the Value JSON object as a single object (ie not broken apart)? ie details: 300-000012 and value: {"is_complete": "False", "is_in_progress": "True"} ?

Nick Over a year ago

@PrashantMarathay sure, you can put it together using JSON_OBJECT. See for example dbfiddle.uk/gILW18NN

Collectives™ on Stack Overflow

Using JSON_TABLE to convert an ARRAY into Columns

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related