How to select keys of objects in a JSON array using MySQL

Question

I'm working with a table that uses a JSON column to store questions and answers as an array of objects where the key is the question and the value is the answer.

The questions are user generated so the keys can literally be anything.

For example:

id	questions
1	[{"Favourite Food?": "Pizza"}, {"Age?": "12"}]
2	[{"Where do you live?": "France"}, {"Are you ok?", "No, not really"}]

I'm trying to figure out if it's possible using MySQL to query this data and get a result that looks like this:

question	answer
"Favourite Food?"	"Pizza"
"Age?"	"12"
"Where do you live?"	"France"
"Are you ok?"	"No, not really"

I didn't think this would be too hard but quickly realized this is probably way out of my depth with MySQL!

The main struggle here is that I can't seem to figure out how to extract the keys when they're buried in an Array like this and I don't know what the names of the keys would be.

The closest I can get is a query that looks like this:

select j.* from ats_applicants,
                JSON_TABLE(additional_fields, "$[*].*" COLUMNS ( question varchar(100) PATH '$', answer varchar(100) PATH '$'))
                    as j where additional_fields is not null;

Problem is this only gives me the values of the objects and not the keys like this:

question	answer
"Pizza"	"Pizza"
"12"	"12"
"etc"	"etc"

I have no good reason for doing this by the way. Purely scratching an itch because I saw this problem come up and thought it would be simple -- and maybe it is -- I just don't see how to do this!

Thanks in advance 🙏

Answer 1

It would be easier if you store the JSON differently:

id	questions
1	[{"question": "Favourite Food?", "answer": "Pizza"}, {"question": "Age?": "answer": "12"}]
2	[{"question": "Where do you live?", "answer": "France"}, {"question": "Are you ok?", "answer": "No, not really"}]

Then you can use JSON_TABLE() to map the fields into columns:

SELECT j.* 
FROM ats_applicants 
CROSS JOIN JSON_TABLE(additional_fields, '$[*]' COLUMNS ( 
    question varchar(100) PATH '$.question', 
    answer varchar(100) PATH '$.answer'
  )
) AS j
WHERE additional_fields IS NOT NULL;

But it would be even easier if you don't use JSON at all, but just store each question and answer in a row by itself:

id	applicant_id	question	answer
1	1	Favorite Food?	Pizza
2	1	Age?	12
3	2	Where do you live?	France
4	2	Are you ok?	No, not really

People are too eager to use JSON these days. Normal rows and columns are easier and more efficient.

---

Re your comment:

Yes, I believe it's possible, but it's more trouble than it's worth.

You can't use JSON_TABLE() directly, because the objects don't have consistent key names.

So you would need to:

1. Split the array into one object per row by joining to a set of integers. For example, use a temp table or a CTE.
2. For each object, use JSON_KEYS() to get the key names into an array.
3. Use those key names in calls to JSON_EXTRACT().

Below is a solution I got to work. To my mind, this is utterly stupid to use a data model that requires such complex code to do the simplest task.

WITH RECURSIVE n AS (
  SELECT 0 AS n
  UNION SELECT n+1 FROM n WHERE n < 1
), 
pair AS (
  SELECT id, n.n, JSON_EXTRACT(additional_fields, CONCAT('$[', n.n, ']')) AS pair
  FROM n CROSS JOIN ats_applicants
),
q AS (
  SELECT id, n, JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(pair), '$[0]')) AS question
  FROM pair
)
SELECT id, q.question, JSON_UNQUOTE(JSON_EXTRACT(pair.pair, CONCAT('$."', q.question, '"'))) AS answer
FROM q JOIN pair USING (id, n)

Output:

+----+--------------------+----------------+
| id | question           | answer         |
+----+--------------------+----------------+
|  1 | Age?               | 12             |
|  1 | Favourite Food?    | Pizza          |
|  2 | Are you ok?        | No, not really |
|  2 | Where do you live? | France         |
+----+--------------------+----------------+