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

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') = '???'

I changed the mysql tag to mariadb, and edited your title and question. MariaDB support for JSON is quite different from MySQL. — Bill Karwin
– Bill Karwin, Commented Jan 21, 2020 at 1:47

Nick · Accepted Answer · 2020-01-21 01:01:27Z

1

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

edited Jan 21, 2020 at 1:01

answered Jan 20, 2020 at 23:55

Nick

147k23 gold badges67 silver badges106 bronze badges

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

6 Comments

altruios Over a year ago

#1210 - Incorrect arguments to ESCAPE not yet getting that to work: keep getting this error in the first section of code. the 'one' parameter is? i understand the name[*].prop notation there.

Nick Over a year ago

@altruios I've updated my demo to be more similar to your situation

Nick Over a year ago

@altruios the 'one' parameter simply tells JSON_SEARCH to stop looking as soon as it finds a single match. That parameter can also be 'all', which will return paths of all matching barcodes. Since you only want an existence check, 'one' is sufficient.

altruios Over a year ago

no more errors on the sql side, but returning empty results when I copy and paste a barcode it has no results. tried multiple values.

altruios Over a year ago

after looking more into the problem - found the answer - turns out it wasn't returning anything because of a white space that was not being cleared on insertion. fixed and working now! well, now onto figuring out how to trim all the white spaces from all the barcode entries in that json structure.

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related