Query a multi-level JSON object stored in MySQL

Question

I have a JSON column in a MySQL table that contains a multi-level JSON object. I can access the values at the first level using the function JSON_EXTRACT but I can't find how to go over the first level.

Here's my MySQL table:

CREATE TABLE ref_data_table (
    `id` INTEGER(11) AUTO_INCREMENT NOT NULL, 
    `symbol` VARCHAR(12) NOT NULL, 
    `metadata` JSON NOT NULL,
    PRIMARY KEY (`id`)
);

Here's my Python script:

import json 
import mysql.connector 

con = mysql.connector.connect(**config) 
cur = con.cursor() 

symbol = 'VXX'
metadata = {
    'tick_size': 0.01, 
    'data_sources': {
        'provider1': 'p1', 
        'provider2': 'p2',
        'provider3': 'p3'
    },
    'currency': 'USD'
}
sql = \
    """
    INSERT INTO ref_data_table (symbol, metadata) 
    VALUES ('%s', %s);
    """
cur.execute(sql, (symbol, json.dumps(metadata)))
con.commit()

The data is properly inserted into the MySQL table and the following statement in MySQL works:

SELECT symbol, JSON_EXTRACT(metadata, '$.data_sources') 
FROM ref_data_table
WHERE symbol = 'VXX';

How can I request the value of 'provider3' in 'data_sources'?

Many thanks!

Merlin · Accepted Answer · 2016-06-10 20:40:25Z

6

Try this:

'$.data_sources.provider3'

SELECT symbol, JSON_EXTRACT(metadata, '$.data_sources.provider3) 
FROM ref_data_table
WHERE symbol = 'VXX';

edited Jun 10, 2016 at 20:40

answered Jun 10, 2016 at 20:22

Merlin

25.8k44 gold badges141 silver badges213 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rumpelstiltskin Koriat · Accepted Answer · 2016-06-10 20:34:06Z

0

the JSON_EXTRACT method in MySql supports that, the '$' references the JSON root, whereas periods reference levels of nesting. in this JSON example

{
    "key": {
        "value": "nested_value"
    }
}

you could use JSON_EXTRACT(json_field, '$.key.value') to get "nested_value"

answered Jun 10, 2016 at 20:34

Rumpelstiltskin Koriat

4314 silver badges7 bronze badges

Collectives™ on Stack Overflow

Query a multi-level JSON object stored in MySQL

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related