Extract data from object out of json array in mySQL [duplicate]

Question

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?

See the linked question for the solution using JSON_TABLE() and an alternative solution using other JSON functions. Spoiler: the alternative solution is no simpler. If you want it to be simple, then don't store records in JSON. Store them in normal rows and columns. Using JSON does not make queries easier — it makes them harder. — Bill Karwin
– Bill Karwin, Commented Jan 14, 2024 at 21:40

Salman Arshad · Accepted Answer · 2024-01-14 11:44:39Z

2

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'

answered Jan 14, 2024 at 11:44

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

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

4 Comments

Thallius Over a year ago

Thanks for the answer but there is no way to use the json functions of MySQL itself?

Salman Arshad Over a year ago

I don't understand. Json_table is built into MySQL

Bill Karwin Over a year ago

@Thallius If you click the link Salman provided above, you see the documentation for the JSON_TABLE() function in the MySQL reference manual. It is a built-in function of MySQL.

Thallius Over a year ago

I know that this is a MySQL function, but it looks so overcomplicated for me that you need to create a table out of an array and do a subselect to be able to find one entry which is clearly defined. Please do not misunderstand me. I thought this could be done with an easy json_search and/or json_extract etc…

Collectives™ on Stack Overflow

Extract data from object out of json array in mySQL [duplicate]

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related