CODEWITHSUNDEEP
phpsqljsonmariadbjson-extract
altruios
altruios

Reputation: 1083

reading/searching through JSON data in MariaDB (multiple objects in arrays: deep search - syntax question)

I have this JSON structure in a field in a table in a MariaDB database:

table column name: BcDJSON

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

my sql query current looks like:

"SELECT * from table WHERE BcDJSON LIKE '%.$value.%'";

but that does not result in stable code. I have tried using JSON_EXTRACT, but going through an object into an array of objects in MariaDB: I have tried a few variations - and reading through the docs has not answered my question. is JSON_EXTRACT even what I really want to use here? I need to return the whole row - where a barcode exists in that JSON structure.

syntaxs things I have tried: (??? = valid values)

SELECT * from datawork WHERE JSON_EXTRACT('BcDJSON', '$.barcode') = '???'
SELECT * from datawork WHERE JSON_EXTRACT('BcDJSON', '$.placards, $.barcode') = '???'
SELECT * from datawork WHERE JSON_EXTRACT('BcDJSON', '$.placards.$.barcode') = '???'
SELECT * from datawork WHERE JSON_EXTRACT('BcDJSON', '$.placards->$.barcode') = '???'
SELECT * from datawork WHERE JSON_EXTRACT('BcDJSON', '$.placards$.barcode') = '???'
SELECT * from datawork WHERE JSON_EXTRACT('BcDJSON', '$.placards') = '???'
SELECT * from datawork WHERE JSON_EXTRACT('BcDJSON', '$.placards', '$.barcode') = '???'
SELECT * from datawork WHERE JSON_EXTRACT('BcDJSON', '$.placards.barcode') = '???'

Upvotes: 1

Views: 2667

Answers (1)

Nick
Nick

Reputation: 147166

You should be using JSON_SEARCH to see if the value can be found in one of the barcode values:

SELECT * 
FROM datawork
WHERE JSON_SEARCH(BcDJSON, 'one', '???', NULL, '$.placards[*].barcode') IS NOT NULL

For example:

CREATE TABLE datawork (id int auto_increment primary key,
                       BcDJSON TEXT);
INSERT INTO datawork (BcDJSON) values 
('{"placards":
     [
     {"barcode":"123","destination":"a","weight":"1"}, 
     {"barcode":"456","destination":"b","weight":"2"}
    ]
}'),
('{"placards":
     [
     {"barcode":"789","destination":"a","weight":"1"}, 
     {"barcode":"123","destination":"b","weight":"2"}
    ]
}');
SELECT * 
FROM datawork 
WHERE JSON_SEARCH(BcDJSON, 'one', '123',  NULL,'$.placards[*].barcode') IS NOT NULL

Output:

id  BcDJSON
1   {"placards": [ {"barcode":"123","destination":"a","weight":"1"}, {"barcode":"456","destination":"b","weight":"2"} ] }
2   {"placards": [ {"barcode":"789","destination":"a","weight":"1"}, {"barcode":"123","destination":"b","weight":"2"} ] }

Demo on dbfiddle

Upvotes: 1

Related Questions