CODEWITHSUNDEEP
mysqlmysql-json
kamilzet_
kamilzet_

Reputation: 192

Retrieve values from mySQL JSON field

Given the following JSON structure stored in one of the MySQL database fields:

{
"ZZZ": {
    "shipp_price_euro": {
        "old": "1",
        "new": "2"
    }
}

}

I would like to get the following tabular output:

enter image description here

I already tried something like this:

SELECT json_extract(logs, "$.shipp_price_euro.old"), json_extract(logs, "$.shipp_price_euro.new") FROM some_table

However this returns NULLS. I think it's because I'm missing a ref to the parent element in my JSON_EXTRACT() path expression.

Do you know how this could be handled?

EDIT 20220526_1:

Please find the schema of the input table:

CREATE TABLE some_table (logs LONGTEXT);

And sample data:

INSERT INTO some_table VALUES
(
'{"XXX": {"shipp_price_euro": {"old": "3", "new": "4"}}, 
  "YYY": {"shipp_price_euro": {"old": "5", "new": "6"}}}'
);

Upvotes: 0

Views: 160

Answers (1)

Akina
Akina

Reputation: 42622

SELECT some_table.id, 
       jsontable1.json_id,
       jsontable2.old,
       jsontable2.new
FROM some_table
CROSS JOIN JSON_TABLE(JSON_KEYS(logs),
                      '$[*]' COLUMNS (json_id VARCHAR(255) PATH '$')) jsontable1
CROSS JOIN JSON_TABLE(JSON_EXTRACT(logs, CONCAT('$.', jsontable1.json_id)),
                      '$.shipp_price_euro' COLUMNS (old INT PATH '$.old',
                                                    new INT PATH '$.new')) jsontable2

MySQL 8.0.20 needed.

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=017486f62a01cbe9b5b68c4604af4e1e

Upvotes: 2

Related Questions