QUERY against nested JSON field, MYSQL, PHP

Question

I have a structure like such:

{"placards":
    [
    {"barcode": "string", "destination":"string", "weight":"string"},
    {etc...}
]
}

stored on a single column in a MYSQL database.

I am trying to search for a specific barcode, and return the entire row where that barcode is inside that json structure in that row.

I have tried two methods, both found on stack overflow but I am not getting a result in one method, and below I will post the error from the latter.

attempt1:
"SELECT * FROM table WHERE placards[*->barcode] = ".$job->events[0]->imcb;


attempt2:
    $sampleBC = $job->events[0]->imcb;
        $sql = 'SELECT * FROM(
        SELECT data_column->"[*]" as row
        from table
        where $sampleBC IN JSON_EXTRACT(data_column, ""
    )
    WHERE row->".barcode" = $sampleBC';

neither of these methods gives an error on $stmt->error, but I am not actually able to successfully grab anything with this query.

the php code that works: $sql= "SELECT * from table where json_contains(jsondata,'{"; $sql.='"barcode":"'.$sampleBC.'"}'; $sql.="', '$.placards')"; — altruios
– altruios, Commented Sep 8, 2020 at 18:33

GMB · Accepted Answer · 2020-09-04 23:33:12Z

2

You can just use json_contains():

select *
from mytable
where json_contains(data_column, '{ "barcode": "foo" }' , '$.placards')

This phrases as: search for any element in the array under path 'placards' that contains the key/value pair(s) of candidate object '{ "barcode": "foo" }', where 'foo' is the barcode value that you search for.

Demo on DB Fiddle:

set @data_column = 
    '{
        "placards": [
            {"barcode": "foo", "destination":"string", "weight":"string"},
            {"barcode": "bar", "destination":"string", "weight":"string"}
        ]
    }';

select json_contains(@data_column, '{ "barcode": "foo" }' , '$.placards') is_a_match;
| is_a_match |
| ---------: |
|          1 |

select json_contains(@data_column, '{ "barcode": "baz" }' , '$.placards') is_a_match;

| is_a_match |
| ---------: |
|          0 |

Starting MySQL 8.0.17, you can even create a multi-valued index on the nested json array, so the database does not need to perform a full table scan for this query:

alter table mytable 
    add index myidx( (cast(data_column -> '$.placards' as unsigned array)) );

edited Sep 4, 2020 at 23:33

answered Sep 4, 2020 at 23:24

GMB

224k25 gold badges103 silver badges151 bronze badges

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

5 Comments

Bill Karwin Over a year ago

Yes, this works as well as my solution, but both your solution and mine are bound to do table-scans.

GMB Over a year ago

@BillKarwin: not anymore in very recent versions...

altruios Over a year ago

here there. there is one error, not in the sql, but in how to represent it in PHP. since it uses both ' and ", and php doesn't have anything other than those two string identifiers... I am having to concatenate.

altruios Over a year ago

$sql= 'SELECT * from datawork where json_contains(barcodeDesitinationsJson,'."'".'{"barcode": "$sampleBC"}'."'".", '$.placards')'"; is what I have, but so far no luck - the query works in the database - so I know I should get a result...

GMB Over a year ago

@altruios: do not concatenate variables in the query string. Use a parameterized query instead. There are plenty of examples here on SO or elsewhere on how to proceed.

Bill Karwin · Accepted Answer · 2020-09-04 23:24:24Z

1

You would have to use JSON_TABLE():

SELECT mytable.* FROM mytable,
  JSON_TABLE(mytable.data_column, '$.placards[*]' COLUMNS (
    barcode VARCHAR(100) PATH '$.barcode',
    destination VARCHAR(100) PATH '$.destination', 
    weight VARCHAR(20) PATH '$.weight'
  )) AS j
WHERE j.barcode = ?

It would be much simpler if you stored those fields in normal columns instead of in JSON. Store one row per placard, with individual columns for barcode, description, and weight.

SELECT m.* FROM mytable AS m JOIN placards AS p ON m.id = p.mytableid 
WHERE p.barcode = ?

Most uses of JSON in MySQL I see in Stack Overflow questions are unnecessary, because the data doesn't need the flexibility of JSON. Queries against JSON documents are hard to write and hard to optimize. JSON also requires more space to store the same data.

answered Sep 4, 2020 at 23:24

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

1 Comment

Bill Karwin Over a year ago

Out of curiosity why are you using JSON for this instead of starting with a normalized table?

Collectives™ on Stack Overflow

QUERY against nested JSON field, MYSQL, PHP

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related