чтение / поиск данных JSON в MariaDB (несколько объектов в массивах: глубокий поиск - вопрос синтаксиса)

У меня есть эта структура JSON в поле таблицы в базе данных MariaDB:

table column name: BcDJSON

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

мой текущий запрос sql выглядит так:

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

но это не приводит к стабильному коду. Я пробовал использовать JSON_EXTRACT, но перебирал объект в массив объектов в MariaDB: я пробовал несколько вариантов - и чтение документации не дало ответа на мой вопрос. JSON_EXTRACT вообще то, что я действительно хочу здесь использовать? Мне нужно вернуть всю строку - если штрих-код существует в этой структуре JSON.

синтаксисы, которые я пробовал: (??? = допустимые значения)

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

json php sql mariadb json-extract
person altruios    schedule 20.01.2020    source источник
comment
Какая версия MySQL?   -  person Nick    schedule 21.01.2020
comment
Версия сервера: 10.3.16-MariaDB PHP: 7.3.6   -  person altruios    schedule 21.01.2020
comment
Я изменил тег mysql на mariadb и отредактировал ваш заголовок и вопрос. Поддержка JSON в MariaDB сильно отличается от MySQL.   -  person Bill Karwin    schedule 21.01.2020

Ответы (1)


arrow_upward
1
arrow_downward

Вы должны использовать _1 _, чтобы узнать, можно ли найти значение в одном из barcode значений:

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

Например:

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

Выход:

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"} ] }

Демо на dbfiddle

person Nick    schedule 20.01.2020
comment
# 1210 - Неверные аргументы ESCAPE еще не заставили это работать: продолжайте получать эту ошибку в первом разделе кода. параметр "один"? Я понимаю обозначение name [*]. prop там. - person altruios; 21.01.2020
comment
@altruios Я обновил свою демонстрацию, чтобы она больше соответствовала вашей ситуации - person Nick; 21.01.2020
comment
@altruios параметр 'one' просто указывает JSON_SEARCH прекратить поиск, как только найдет одно совпадение. Этот параметр также может быть 'all', который вернет пути для всех совпадающих штрих-кодов. Поскольку вам нужна только проверка существования, достаточно 'one'. - person Nick; 21.01.2020
comment
@altruios Это ответ на ваш вопрос? Если нет, не могли бы вы предоставить дополнительную информацию, чтобы ответить на него? В противном случае отметьте ответ как принятый (галочка под стрелками "за" / "против"). См. stackoverflow.com/help/someone-answers. - person Nick; 21.01.2020
comment
больше нет ошибок на стороне sql, но возвращая пустые результаты, когда я копирую и вставляю штрих-код, он не дает результатов. пробовал несколько значений. - person altruios; 21.01.2020
comment
после более подробного изучения проблемы - нашел ответ - оказалось, что он ничего не возвращал из-за пустого пространства, которое не очищалось при вставке. исправлено и работает сейчас! Что ж, теперь выясним, как обрезать все пробелы во всех записях штрих-кода в этой структуре json. - person altruios; 21.01.2020
comment
Приятно слышать, но какая боль по поводу белого пространства. К сожалению, заменить подстановочные знаки в функциях JSON MariaDB невозможно, но, возможно, вы могли бы использовать что-то вроде REGEXP_REPLACE(BcDJSON, '"barcode"\\s*:\\s*"\\s*([^ "]+)\\s*"', '"barcode":"\\1"') для обрезки, например. dbfiddle.uk/ - person Nick; 22.01.2020