Extract data from one special object out of JSON array in mySQL

[Thallius]

Hi

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"
    },
   ....
]

I need a select, which gives me the id of the object which is of type master.

How can I do this?

[m_hutley]

can you guarantee that the type property comes before the id property? A lazy regex would find it… probably not efficient, but it should work…

[evershinewalls0]

SELECT your_json_column->‘$.your_object_key’ AS extracted_data
FROM your_table
WHERE your_condition;

[rpkamp]

@evershinewalls0

WHERE your_condition;

So what would the condition be?

[m_hutley]

It is ugly as sin. Basically, you’re gonna have to explode the thing into a subtable.

I missed that this column was a JSON type.

SELECT jt.id 
FROM yourtable 
JOIN JSON_TABLE(yourtable.yourcolumn,
     '$[*]' 
     COLUMNS(
       type VARCHAR(20) PATH '$.type', 
       id varchar(10) PATH '$.id' 
     )
) jt 
WHERE jt.type = "master"

DB Fiddle - SQL Database Playground

(Note: This will only work in MySQL 8)

[Thallius]

Yes I have already found the same solution thanks

[rpkamp]

Ouch. Performance of that isn’t going to be amazing methinks.

[m_hutley]

Yeah, I dont fancy the performance on bigger tables/objects. But… thats what you get for using a JSON column instead of breaking the object down into table structure in the first place?

[Thallius]

It will be only a few (around 20-50) entries which need to be checked. So not a problem and a not need an extra table.

[rpkamp]

For 20-50 entries you won’t notice a difference. You’ll probably only start noticing the difference for 1000+ rows.

Still, good to know for anybody reading along here.

[Lemon_Juice]

This is an interesting example of json over-use in sql. Even if the provided solution works well for the given amount of data it is harder to understand, harder to code and harder to maintain than a simple table would be. I would never use a json column to store such data. In this case I consider solutions with JSON_TABLE and the like viable only for extreme cases, e.g. when I’m working with data from an external system and I have no control over how it is stored. Otherwise, i’d stay away from json.

In my own development I’ve used json for very simple use cases like storing a simple array of tags attached to a row (to an order, invoice, etc.) that I’m almost 100% certain I will not need to search for. I once tried using a json column to store a multidimensional array representing structured data just to see what it’s like and while it works this is the part of my code I don’t like to come back to or modify because it is much harder to understand and debug than if I stored the data in separate tables.

[m_hutley]

i’m… fine with putting JSON into a table… but if i’m doing it, basically its a TEXT column, and i’m just using it to store a JSON for retrieval and parsing/handling in the mezzanine level.

[Thallius]

Storing a json object in a column is fine. I was thinking like @Lemon_Juice once before but making some tests showed, that searching for values in a json object is as fast as searching for the value of a column, some times even faster (please don’t ask me why but we made tests with many different combinations and sizes up to million of rows)

Storing a json array in a field is nothing I would do also normally. But in this case it is ok because the data is only needed once (so really only one time I request it after it was written)

[Lemon_Juice]

@Thallius

searching for values in a json object is as fast as searching for the value of a column

I think performance is not relevant unless the amount of data is huge. Still, I would use a separate table for the ease of use and not for performance. Additionally, database can keep referential integrity and data structure for free then why not take advantage of it? A json column will accept any garbage data without complaining.

But I must admit that keeping data in the same table sometimes is enticing because I as a developer can see it all in one view in a database admin tool when browsing a table and I don’t have to switch to another table and do lookups to see related data - this is a plus. I think for simple structures it might be okay. Myself am guilty of this and when a user could have several email addresses or phone numbers I stored them in a json array or even in a text field as comma-separated values - just because creating additional tables for such simple structures when there’s no requirement for searching seemed too much hassle. For me your case is on the edge of what I’d consider acceptable, everyone will feel different on that matter.

Anyway, this reminds me of Wordpress, whose developers push this idea to the extreme by storing complex objects in text fields as PHP serialized strings. Not only can’t you use SQL json functions to query and extract the data but even changing something by hand is very difficult and error prone if you don’t run the values through appropriate functions in PHP. That’s why I believe if you want to learn worst coding practices - look at Wordpress!