CODEWITHSUNDEEP
mysqlcommon-schema
Kalyan
Kalyan

Reputation: 263

Using common_schema library not able to parse the json array values

We are using this common_schema library in mysql 5.6 to extract the values from json array. The format is given below. But it returns the NULL value. So, can you please help us out how to parse the json array using common_schema.

select common_schema.extract_json_value('"batter":
[
     { "id": "1001", "type": "Regular" },
     { "id": "1002", "type": "Chocolate" },
     { "id": "1003", "type": "Blueberry" },
     { "id": "1004", "type": "Devils Food" }
]','/id');

Expected output should be saved in table as

   id    type
  1001  Regular
  1002  Chocolate
  1003  Blueberry
  1004  Devils Food

Please let us know how we can achieve this parsing.

Thanks Kalyan

Upvotes: 1

Views: 774

Answers (1)

wchiquito
wchiquito

Reputation: 16559

Directly it seems not so easy to get what you need.

An option to obtain a single value is:

SET @`json` := '
{
    "batter":
        [
            { "id": "1001", "type": "Regular" },
            { "id": "1002", "type": "Chocolate" },
            { "id": "1003", "type": "Blueberry" },
            { "id": "1004", "type": "Devils Food" }
        ]
}
';

SELECT
    `common_schema`.`extract_json_value`(@`json`,'descendant-or-self::id[1]') `id`,
    `common_schema`.`extract_json_value`(@`json`,'descendant-or-self::type[1]') `type`;

+------+---------+
| id   | type    |
+------+---------+
| 1001 | Regular |
+------+---------+
1 row in set (0,04 sec)

Upvotes: 0

Related Questions