CODEWITHSUNDEEP
mysqlarraysjsonjson-extract
Guilherme Mascarenhas
Guilherme Mascarenhas

Reputation: 73

read json array on mysql query

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

Upvotes: 5

Views: 21388

Answers (2)

SagitSri
SagitSri

Reputation: 521

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

Upvotes: 1

Rahul Singh
Rahul Singh

Reputation: 710

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       |

Upvotes: 9

Related Questions