How to access nested properties in mysql json column?

@orpha 

To access nested properties in a MySQL JSON column, you can use the -> or ->> operator along with the key path to navigate through the nested structure.

Here is an example of accessing a nested property in a JSON column:

Suppose you have a table called users with a column named data of type JSON. The data column contains nested JSON data like this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

{
   "name": "John",
   "address": {
      "street": "123 Main St",
      "city": "New York",
      "state": "NY"
   },
   "contacts": [
      {
         "name": "Jane",
         "phone": "123-456-7890"
      },
      {
         "name": "Bob",
         "phone": "987-654-3210"
      }
   ]
}

To access the nested property street in the address, you can use the -> operator:

1
2

SELECT data->"$.address.street" AS street
FROM users;

To access the nested property phone in the second element of the contacts array, you can use the ->> operator:

1
2

SELECT data->"$.contacts[1].phone" AS phone
FROM users;

You can also use the -> or ->> operators in combination to access multiple nested properties. For example, to access the nested property city and state in the address, you can do:

1
2
3

SELECT data->"$.address.city" AS city,
       data->"$.address.state" AS state
FROM users;

Note that if you are using MySQL 8.0 or higher, you can also use the ->> operator instead of -> to directly return the value as a string instead of a JSON object.