title: Query a multi-level JSON object stored in MySQL
body: <p>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 <code>JSON_EXTRACT</code> but I can't find how to go over the first level. </p>
  
  <p>Here's my MySQL table: </p>
  
  <pre><code>CREATE TABLE ref_data_table (
      `id` INTEGER(11) AUTO_INCREMENT NOT NULL, 
      `symbol` VARCHAR(12) NOT NULL, 
      `metadata` JSON NOT NULL,
      PRIMARY KEY (`id`)
  ); 
  </code></pre>
  
  <p>Here's my Python script: </p>
  
  <pre><code>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()
  </code></pre>
  
  <p>The data is properly inserted into the MySQL table and the following statement in MySQL works: </p>
  
  <pre><code>SELECT symbol, JSON_EXTRACT(metadata, '$.data_sources') 
  FROM ref_data_table
  WHERE symbol = 'VXX';
  </code></pre>
  
  <p>How can I request the value of 'provider3' in 'data_sources'? </p>
  
  <p>Many thanks! </p>