Extract JSON Value Using SQL

This document contains informatoin on how we can extract information stored in form of JSON array. we will demonstrate how to use the JSON_EXTRACT()

How JSON data is stored in MySQL

JSON data in MySQL is treated as its own data type, a JSON string, and can appear in 2 main forms:

Key-value object: a single record which consists of multiple named or indexed fields (or keys) paired with values Nested Array/Table: a table built with multiple key-value objects in a hierarchical format

What JSON objects look like

{
  "Person": {
    "Name": "Homer",
    "Age": 39,
    "Hobbies": [
      "Eating",
      "Sleeping"
    ]
  }
}

In MySQL, the JSON_EXTRACT() function returns data from a JSON document. The actual data returned is determined by the path you provide as an argument.

You provide the JSON document as the first argument, followed by the path of the data to return. You can also provide multiple paths to return data from multiple places in the document.

Syntax

The syntax goes like this:

JSON_EXTRACT(json_doc, path[, path] ...)

Where json_doc is the JSON document and path is the path to the value to return.

Example

Here’s an example with a JSON document.

SET @data = '{  
    "Person": {    
       "Name": "Homer", 
       "Age": 39,
       "Hobbies": ["Eating", "Sleeping"]  
    }
 }';
SELECT JSON_EXTRACT(@data, '$.Person.Name', '$.Person.Age', '$.Person.Hobbies') AS 'Result';

Copy this query and run this in MySQL query window, you will see the output displayed value of key named Name which is Home as shown in below image,

References

Here is the list of references for your references;

Refer this video where you can find more detail on this function and different ways to get json data.