Select and insert JSON data in MySQL

Handle the JSON datatype in MySQL

Create a table with JSON datatype:

CREATE TABLE test_json (testdoc JSON);

Insert data into JSON table:

INSERT INTO test_json VALUES('{"key1": "value1", "key2": "value2"}');

Select the JSON table:

select * from test_json;

testdoc                             |
{"key1": "value1", "key2": "value2"}|

Extract data from JSON Column:

select json_Extract(testdoc,'$.key1') from test_json;

"value1"                      |

Use JSON column data in where clause with JSON_EXTRACT function:

INSERT INTO test_json VALUES('{"key1": "value3", "key2": "value4"}');

SELECT  testdoc
FROM test_json 
WHERE json_extract(testdoc, '$.key1') LIKE '%value1%';

testdoc                             |
{"key1": "value1", "key2": "value2"}|

Example of using JSON_EXTRACT function:

JSON Object

SELECT JSON_EXTRACT('{"key1": "value3", "key2": "value4"}','$.key1');

JSON_EXTRACT('{"key1": "value3", "key2": "value4"}','$.key1')|
"value3"                                                     |

using index:
SELECT JSON_EXTRACT('["value1","value2", "value3","value4"]','$[0]');

JSON_EXTRACT('["value1","value2", "value3","value4"]','$[0]')|
"value1"                                                     |

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.