sqlmysqlmysql-json

Extract data from object out of json array in mySQL

I have a mySQL database with a table named “orders”. In this table I have a column named ids which is JSON.

The JSON looks like

[
    {
         "type" : "master",
         "id" : "100"
    },
    {
         "type" : "slave",
         "id" : "101"
    },
    {
         "type" : "slave",
         "id" : "102"
    },
    ....
]

There is always only one entry a master. I need a select, which gives me the id of the object which is of type master.

How can I do this?

Solution

  • You can use MySQL JSON_TABLE function to convert the JSON into rows/columns, then apply a WHERE clause:

    SELECT *
FROM orders, json_table(
    orders.ids,
    '$[*]'
    COLUMNS(
        type VARCHAR(100) PATH '$.type',
        id VARCHAR(100) PATH '$.id'
    )
) AS jt
WHERE jt.type = 'master'