How can I query fields within a JSON object in MySQL?

1 year ago
Ava Mitchell
2 minutes

In MySQL, you can retrieve data from a JSON field using the JSON_EXTRACT function. This function takes two parameters: the first parameter is the JSON field you want to query, and the second parameter is the key path you want to extract.

For example, let’s say there is a JSON field named ‘data’ that contains the following information:

{
   "name": "John",
   "age": 30,
   "address": {
      "city": "New York",
      "state": "NY"
   }
}

To retrieve the value of the ‘name’ key in the data field, you can use the following query statement:

SELECT JSON_EXTRACT(data, '$.name') AS name FROM table_name;

The result will return the value of the key “name”.

If you want to retrieve the value of a key in a nested JSON field, you can use dot notation to indicate the nested levels. For example, to retrieve the value of the city key under the address key in the data field, you can use the following query statement:

SELECT JSON_EXTRACT(data, '$.address.city') AS city FROM table_name;

The result will return the value of the key “city”.

If you want to retrieve values from a JSON array, you can use an index number. For example, if the data field contains a JSON array called hobbies:

{
   "name": "John",
   "age": 30,
   "hobbies": ["swimming", "reading", "traveling"]
}

You can use the following query statement to retrieve the first value in the hobbies array:

SELECT JSON_EXTRACT(data, '$.hobbies[0]') AS first_hobby FROM table_name;

The result will return the first value in the hobbies array.

Please note that the “table_name” in the above query should be replaced with the actual name of the table.

bannerAds