How to use MySQL select statement to generate rows for each entry in a JSON list? -


i have json column in mysql table.

a column contains list of dictionaries. each item in list it's own row, row id. see example below better understanding.

example:

mysql> select id, geography region; +------|-------------------------------------------------------------------------------------------------------------+ |  id  | geography                                                                                                   | +------|-------------------------------------------------------------------------------------------------------------+ |  1   | [{"state": "sc", "county": "berkeley"}}]                                                                    | |  2   | [{"county": "placer", "state": "ca"}, {"county": "sacramento", "state": "ca"}]                              | |  3   | [{"county": "jeff", "state": "mo"},{"county": "charles", "state": "mo"},{"county": "louis", "state": "mo"}] | +------|-------------------------------------------------------------------------------------------------------------+

desired output:

+------|--------------|-------------+ | id   | county       |  state      | +------|--------------|-------------| | 1    | berkeley     |  sc         | | 2    | placer       |  ca         | | 2    | sacramento   |  ca         | | 3    | jeff         |  mo         | | 3    | charles      |  mo         | | 3    | louis        |  mo         | +------|--------------|-------------+

it seems bad idea that, have to, guess.

3 options come mind:

  1. if you're using mysql >=5.7.8, use internal json-related functions
  2. json udf mysql
  3. use common_schema

your best shot 3rd option. should lookup common_schema.extract_json_value().


Comments

Post a Comment