SQL query to parse JSON

Question

I have the user table with user_id and user_details. it contains the JSON data in string format as shown below:

1-

[{"name":"question-1","value":"sachin","label":"Enter your name?"},
    {"name":"question-2","value":"abc@example.com","label":"Enter your email?"},
    {"name":"question-3","value":"xyz","label":"Enter your city?"}]

2-

[{"name":"question-1","value":"sachin123","label":"Enter your name?"},
    {"name":"question-2","value":"abc@example.com","label":"Enter your email?"},
    {"name":"question-3","value":"xyz","label":"Enter your city?"},
    {"name":"question-4","value":"red","label":"Enter your favourite color?"}]

3-

[{"name":"question-1","value":"","label":"Enter your name?"},
    {"name":"question-3","value":"xyz","label":"Enter your city?"},
    {"name":"question-4","value":"red","label":"Enter your favourite color?"}]

4-

[{"name":"question-1","value":"","label":"Enter your name?"},
    {"name":"question-2","value":"pqr@example.com","label":"Enter your email?"},
    {"name":"question-3","value":"abc","label":"Enter your city?"},
    {"name":"question-4","value":"pink","label":"Enter your favourite color?"}]

The expected output is:

row  Enter your name? | Enter your email?   | Enter your city?  | Enter your favourite color?
1    sachin           | abc@example.com     | xyz               | -
2    sachin123        | xyz@example.com     | xyz               | red
3    -                | -                   | xyz               | blue
4    -                | pqr@example.com     | abc               | pink

I have tried doing it through PHP, it is possible by converting JSON to array and then comparing it. Is there a simpler way to get it directly using MySQL query?

Answer 1

I came up with this solution:

SELECT user_id,
    COALESCE(NULLIF(JSON_UNQUOTE(JSON_EXTRACT(user_details, CONCAT(q1, '.value'))), ''), '-') AS 'Enter your name?',
    COALESCE(NULLIF(JSON_UNQUOTE(JSON_EXTRACT(user_details, CONCAT(q2, '.value'))), ''), '-') AS 'Enter your email?',
    COALESCE(NULLIF(JSON_UNQUOTE(JSON_EXTRACT(user_details, CONCAT(q3, '.value'))), ''), '-') AS 'Enter your city??',
    COALESCE(NULLIF(JSON_UNQUOTE(JSON_EXTRACT(user_details, CONCAT(q4, '.value'))), ''), '-') AS 'Enter your favorite color?'
FROM (
    SELECT user_id, user_details,
    SUBSTRING_INDEX(JSON_UNQUOTE(JSON_SEARCH(user_details, 'one', 'question-1')), '.', 1) AS q1,
        SUBSTRING_INDEX(JSON_UNQUOTE(JSON_SEARCH(user_details, 'one', 'question-2')), '.', 1) AS q2,
        SUBSTRING_INDEX(JSON_UNQUOTE(JSON_SEARCH(user_details, 'one', 'question-3')), '.', 1) AS q3,
        SUBSTRING_INDEX(JSON_UNQUOTE(JSON_SEARCH(user_details, 'one', 'question-4')), '.', 1) AS q4
    FROM users) t

Output:

+---------+------------------+-------------------+-------------------+----------------------------+
| user_id | Enter your name? | Enter your email? | Enter your city?? | Enter your favorite color? |
+---------+------------------+-------------------+-------------------+----------------------------+
|       1 | sachin           | abc@example.com   | xyz               | -                          |
|       2 | sachin123        | abc@example.com   | xyz               | red                        |
|       3 | -                | -                 | xyz               | red                        |
|       4 | -                | pqr@example.com   | abc               | pink                       |
+---------+------------------+-------------------+-------------------+----------------------------+

But this doesn't label the columns dynamically from the label found in each question. It's problematic to do that, because there's no guarantee that the label corresponding to question-1 is the same on every row, or present on any given row. So you're going to have to decide what the label should be and hard-code the column aliases in the query before you prepare() the query.

Ultimately, it's probably simpler to do this in PHP as you had already done.