Unable to extract mysql json data while using where clause

Question

I have a table with the below json data type column in a table TABLESDATA

create table tablesdata(sample json);

And I am using the sql to query the table.

select sample->>"$[*].name" as new_data from tablesdata WHERE sample ->>'$[*].name' = "EDFG";

The sample json data is like below

[
    {
        "name": "EDFG",
        "isUsed": true,
        "columns": [
            {
                "name": "Article_color_lookup_id",
                "isUsed": false,
                "dataType": "INTEGER"
            },
        ]
    },
    {
        "name": "ABCDE",
        "isUsed": false,
        "columns": [
            {
                "name": "Article_lookup_criteria_id",
                "isUsed": false,
                "dataType": "INTEGER"
            },
        ]
    },
.............so on
]

But the query is not returning any rows. What am I doing wrong here?

I need the output will be like

[
    {
        "name": "EDFG",
    },
    {
        "name": "EDFG",
    },
]

Answer 1

Can you please try using JSON search functions instead of where clause https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html

Like following some example

Search all items with the 'JavaScript' tag

SELECT * FROM `table` WHERE JSON_CONTAINS(json_field, '["JavaScript"]');

Find all items with tags starting 'Java':

SELECT * FROM `table` WHERE JSON_SEARCH(json_field, 'one', 'Java%') IS NOT NULL;

Answer 2

The result of sample->>"$[*].name" is like an array so the result of where condition will be false ["EDFG", "ABCDE"] != "EDFG"

in your example it would be like

["EDFG", "ABCDE"]

Now you can search in array using json_search

select sample->"$[*].name" as new_data from tablesdata where JSON_SEARCH(sample, 'all', 'EDFG') is not null

---

Update

If you need to search for exactly matches you may search like this

select sample->>"$[*].name" as new_data from tablesdata WHERE sample ->>'$[*].name' = '["EDFG"]';