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, Jan 21 '20 at 01:47

Nick · Answer 1 · 2020-01-21T01:01:27.333

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 '20 at 01:01

answered Jan 20 '20 at 23:55

Nick

138,499
22
57
95

#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. – altruios Jan 21 '20 at 00:02
@altruios I've updated my demo to be more similar to your situation – Nick Jan 21 '20 at 00:04
1

@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. – Nick Jan 21 '20 at 00:08
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 Jan 21 '20 at 15:58
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. – altruios Jan 21 '20 at 16:07
Good to hear but what a pain about the white space. Unfortunately it's impossible to do wildcard replaces in MariaDB JSON functions, but perhaps you could use something like `REGEXP_REPLACE(BcDJSON, '"barcode"\\s*:\\s*"\\s*([^ "]+)\\s*"', '"barcode":"\\1"')` to do the trimming e.g. https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=d7c6789371037b225756dcecb48b161f – Nick Jan 21 '20 at 21:42

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

1 Answers1