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
– Abu Sufian, Commented Jul 20, 2020 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
– Guilherme Mascarenhas, Commented Jul 20, 2020 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
– Bill Karwin, Commented Jul 20, 2020 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
– Guilherme Mascarenhas, Commented Jul 20, 2020 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
– Bill Karwin, Commented Jul 20, 2020 at 20:19

Rahul Singh · Accepted Answer · 2020-07-20 21:17:43Z

10

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       |

edited Jul 20, 2020 at 21:17

answered Jul 20, 2020 at 17:42

Rahul Singh

7301 gold badge6 silver badges11 bronze badges

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

4 Comments

Guilherme Mascarenhas Over a year ago

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

Rahul Singh Over a year ago

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 Over a year ago

@ Guilherme Mascarenhas :Did the solution for MariaDb work for you?

Guilherme Mascarenhas Over a year ago

works like a charm! I didn't know the sequence function on MariaDB... For anyone who want more details about it > mariadb.com/kb/en/sequence-storage-engine

SagitSri · Accepted Answer · 2022-07-15 13:52:38Z

1

Json Details:

"customers": [
    {
        "email": "[email protected]",
        "fullName": "Kar**",
        "mobilePhone": "63968**"
    },{
        "email": "[email protected]",
        "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

answered Jul 15, 2022 at 13:52

SagitSri

5316 silver badges16 bronze badges

1 Comment

ruffin Dec 20, 2024 at 22:35

Are you crediting the other answer to this question, written by Rahul Singh? That's a first... Was there something new you wanted your answer to highlight that was missing in that answer?

Collectives™ on Stack Overflow

read json array on mysql query

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related