JSON Elements to Row OR JSON to table Row in MYSQL(Export JSON in MySQL)

Question

We could have many request to store json to database. sometime we need to convert JSON Key set to Table - Rows. How to extract complete json in Mysql table?

i.e. Having below json and requirement is to store each set as a row in the table.

'{
"log": [
{
"datetime": "2017-10-25 07:19:55",
"Activity": "Activity 1"
},
{
"datetime": "2017-10-25 07:20:05",
"Activity": "Activity 2"
},
{
"datetime": "2017-10-25 07:20:31",
"Activity": "Activity 3"
},
{
"datetime": "2017-10-25 07:20:31",
"Activity": "Activity 4"
},
{
"datetime": "2017-10-25 07:21:03",
"Activity": "Activity 5"
},
{
"datetime": "2017-10-25 13:56:42",
"Activity": "Activity 6"
},
{
"datetime": "2017-10-25 13:56:53",
"Activity": "Activity 7"
},
{
"datetime": "2017-10-25 13:57:03",
"Activity": "Activity 8"
},
{
"datetime": "2017-10-25 13:57:04",
"Activity": "Activity 9"
}, 
{ "datetime": "2017-10-25 13:57:53",
"Activity": "Activity 10"
},
{
"datetime": "2017-10-25 13:57:59",
"Activity": "Activity 11"
},
{
"datetime": "2017-10-25 13:58:01",
"Activity": "Activity 12"
},
{
"datetime": "2017-10-25 13:58:01",
"Activity": "Activity 13"
}
]
} '

score 1 · Answer 1 · answered Dec 26 '18 at 06:40

1

MySQL 8.0 has a JSON_TABLE function:

INSERT INTO NewTable 
  SELECT * FROM JSON_TABLE('...your JSON ...', '$.log[*]' columns(
    rowid for ordinality, 
    dt datetime path '$.datetime', 
    activity varchar(20) path '$.Activity')
  ) as t

answered Dec 26 '18 at 06:40

Bill Karwin

538,548
86
673
828

score 0 · Answer 2 · answered Dec 26 '18 at 05:32

JSON can be stored in database with different datatypes. i.e. JSON, VACRHAR, TEXT...etc. For exporting a report or filtering the JSON element values, we should have different solutions to get it done.

Creating a TABLE to store JSON in MySQL table.

CREATE TABLE test_Json(id INT, activity_log JSON);

Inserting a sample data into the table. JSON field data is inserted in JSON datatype, but it can be in VARCHAR or Text type as well.

INSERT INTO test_Json(id, activity_log)
VALUES(1,
'{
"log": [
{
"datetime": "2017-10-25 07:19:55",
"Activity": "Activity 1"
},
{
"datetime": "2017-10-25 07:20:05",
"Activity": "Activity 2"
},
{
"datetime": "2017-10-25 07:20:31",
"Activity": "Activity 3"
},
{
"datetime": "2017-10-25 07:20:31",
"Activity": "Activity 4"
},
{
"datetime": "2017-10-25 07:21:03",
"Activity": "Activity 5"
},
{
"datetime": "2017-10-25 13:56:42",
"Activity": "Activity 6"
},
{
"datetime": "2017-10-25 13:56:53",
"Activity": "Activity 7"
},
{
"datetime": "2017-10-25 13:57:03",
"Activity": "Activity 8"
},
{
"datetime": "2017-10-25 13:57:04",
"Activity": "Activity 9"
}, { "datetime": "2017-10-25 13:57:53",
"Activity": "Activity 10"
},
{
"datetime": "2017-10-25 13:57:59",
"Activity": "Activity 11"
},
{
"datetime": "2017-10-25 13:58:01",
"Activity": "Activity 12"
},
{
"datetime": "2017-10-25 13:58:01",
"Activity": "Activity 13"
}
]
} ');

INSERT INTO test_Json(id, activity_log)
VALUES(2,'
{
"log": [
{ "datetime": "2017-10-29 21:56:27",
"Activity": "Activity-11"
},
{
"datetime": "2017-10-29 21:56:27",
"Activity": "Activity-12"
},
{
"datetime": "2017-10-29 21:56:27",
"Activity": "Activity-13"
},
{
"datetime": "2017-10-29 21:56:27",
"Activity": "Activity-14"
},
{
"datetime": "2017-10-29 21:56:35",
"Activity": "Activity-15"
},
{
"datetime": "2017-10-29 21:56:49",
"Activity": "Activity-16"
},
{
"datetime": "2017-10-29 21:56:49",
"Activity": "Activity-17"
},
{
"datetime": "2017-10-29 21:56:49",
"Activity": "Activity-18"
}
]
}');

Query to get JSON Exported as JSON Array with JSON Length value

SELECT TJ.id,
JSON_LENGTH(JSON_EXTRACT(TJ.activity_log, '$**.datetime')) as Json_Length,
JSON_EXTRACT(TJ.activity_log, '$**.datetime') as json_array_message,
JSON_EXTRACT(TJ.activity_log, '$**.Activity') as json_array_datetime
FROM test_Json TJ
WHERE JSON_VALID(TJ.activity_log) = 1;

JSON_VALID() will validate JSON column data, whether JSON data is proper or not. If not, than it will be filtered out from query.

Recursive CTE (Common Table Expression) to get n-Level element values in row format

WITH RECURSIVE CTE_json (id, array_datetime, array_message,seq) AS
(
SELECT id, 
JSON_EXTRACT(JSON_EXTRACT(activity_log, '$**.datetime'), CONCAT('$[', 0, ']')), 
JSON_EXTRACT(JSON_EXTRACT(activity_log, '$**.Activity'), CONCAT('$[', 0, ']')), 
0 as seq
FROM test_Json
UNION ALL
SELECT TJ.id, 
JSON_EXTRACT(JSON_EXTRACT(TJ.activity_log, '$**.datetime'), CONCAT('$[', rc.seq +1, ']')) as array_datetime, 
JSON_EXTRACT(JSON_EXTRACT(TJ.activity_log, '$**.Activity'), CONCAT('$[', rc.seq +1, ']')) as array_message, 
rc.seq +1 AS seq
FROM CTE_json rc
INNER JOIN test_Json TJ ON rc.id = TJ.id
WHERE rc.seq+1 < JSON_LENGTH(JSON_EXTRACT(TJ.activity_log, '$**.datetime'))
)

SELECT *
FROM CTE_json cj
ORDER BY id, seq;

If current MySQL version doesn't support recursive CTE, than we can use other LOOP alternatives.

JSON Elements to Row OR JSON to table Row in MYSQL(Export JSON in MySQL)

2 Answers2