read json array on mysql query

Question

I know about the possiblity of duplicity of question but i don't found anything to help me on my situation about it.

I have this json on my mysql column:

[
  {
    "ddi": "55",
    "routing_id": "7",
    "price": {
      "mt": 0.0285,
      "mo": 0.0285
    }
  },
  {
    "ddi": "598",
    "routing_id": "10",
    "price": {
      "mt": 0.06,
      "mo": 0.06
    }
  }
]

When i do the following query:

SELECT JSON_EXTRACT(my_column,'$[*].ddi') as ddi FROM my_table

I get the result:

["55", "598"]

My question is: There is a way to return this data on rows instead a json array? Like:

Ddi
55
598

why you need data in this format? After getting json data you can manipulate that data according to your need — Abu Sufian, Jul 20 '20 at 17:17
Hi @AbuSufian maybe i'm being wrong about the way to do it but with this data i pretend make a INNER JOIN" on another table to get the country name by ddi. I'm trying to do this by one single query instead of manipulate the json and do another query. — Guilherme Mascarenhas, Jul 20 '20 at 17:21
If you need to access individual fields within your JSON for joins, it's a red flag for database design. You should structure the data in a normalized fashion instead of using JSON. — Bill Karwin, Jul 20 '20 at 17:51
@BillKarwin hmm ok... based on your comment, the better way to do this is manipulate the json and then make a new one query to achieve my intentions right? — Guilherme Mascarenhas, Jul 20 '20 at 17:54
That's not what I mean. I mean **do not use JSON to store structured data** if you want to query individual elements later. Use normal columns and rows. In this case, you need a table with one row per `ddi` and another table with one row per price for each `ddi`. Using JSON is convenient for storing a complex nested structure of data in one step, but your question is an example of where storing data in JSON instead of in normal form makes it much _harder_ to query later. — Bill Karwin, Jul 20 '20 at 20:19

Rahul Singh · Accepted Answer · 2020-07-20T21:17:43.327

You can use JSON_TABLE for the same

SELECT get_ddi.* 
FROM my_table, 
     JSON_TABLE(my_column, '$[*]' COLUMNS (
                my_column VARCHAR(40)  PATH '$.ddi')
     ) get_ddi;

**Schema (MySQL v8.0)**

    DROP TABLE IF EXISTS `my_table`;
    
    CREATE TABLE IF NOT EXISTS `my_table` (
      `id` SERIAL,
      `my_column` JSON
    );
    
    INSERT INTO `my_table` (`id`, `my_column`)
    VALUES
      (1, '[
      {
        "ddi": "55",
        "routing_id": "7",
        "price": {
          "mt": 0.0285,
          "mo": 0.0285
        }
      },
      {
        "ddi": "598",
        "routing_id": "10",
        "price": {
          "mt": 0.06,
          "mo": 0.06
        }
      }
    ]');

**Query #1**

    SELECT get_ddi.* 
    FROM my_table, 
         JSON_TABLE(my_column, '$[*]' COLUMNS (
                    my_column VARCHAR(40)  PATH '$.ddi')
         ) get_ddi;

**Output**

| my_column |
| --------- |
| 55        |
| 598       |

As per @Guilherme Mascarenhas comments below, the solution needed was for MariaDb. As of version 10.2.31, JSON_TABLE function doesn't exist for MariaDB. A hacky solution could be to use appropriate mariadb sequence table(Depending on the number of rows). JSON_UNQUOTE removes the quote from the extracted value. seq is used as index to get the specified ddi value from the array.

SELECT 
    JSON_UNQUOTE(JSON_EXTRACT(t.my_column, CONCAT('$[', seq_0_to_100.seq, '].ddi'))) AS getddi
FROM my_table t
JOIN seq_0_to_100
HAVING getddi IS NOT NULL;

**Output**

| my_column |
| --------- |
| 55        |
| 598       |

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(my_column, '$[*]' COLUMNS ( my_column VARCHAR(40) PATH '$.' at line 3 0.156 sec I'm using: Mysql MariaDB Version 10.2.31 — Guilherme Mascarenhas, Jul 20 '20 at 18:37
You didn't tag or mention MariaDB in your question. The above mentioned solution works for MySQL v8.0 . MariaDB does not have a JSON_TABLE function. Will update the post for MariaDB. — Rahul Singh, Jul 20 '20 at 20:25
@ Guilherme Mascarenhas :Did the solution for MariaDb work for you? — Rahul Singh, Jul 22 '20 at 16:33
works like a charm! I didn't know the sequence function on MariaDB... For anyone who want more details about it > https://mariadb.com/kb/en/sequence-storage-engine/ — Guilherme Mascarenhas, Jul 24 '20 at 14:14

score 1 · Answer 2 · answered Jul 15 '22 at 13:52

Json Details:

"customers": [
    {
        "email": "kar@mailinator.com",
        "fullName": "Kar**",
        "mobilePhone": "63968**"
    },{
        "email": "kar1@mailinator.com",
        "fullName": "Kar1**",
        "mobilePhone": "639681**"
    }

]

Assume in the table there are 10 rows having a attributes as json colum and having above data in it. I like to get all mobilePhone value from each rows customers json array in a table manner:

SELECT mobilePhone.* 
FROM mh_customer_targets , 
     JSON_TABLE(attributes, '$.customers[*]' COLUMNS (
                attributes VARCHAR(40)  PATH '$.mobilePhone')
     ) mobilePhone

Result:

attributes
63968**
639681**

Credits: Rahul Singh

read json array on mysql query

2 Answers2