#json #sql-server
Вопрос:
Итак, у меня есть таблица базы данных со строковым полем, которое должно содержать правильно сформированный json. У меня также есть динамический конструктор запросов, который будет запрашивать json, он создается во время выполнения, поэтому для каждого фильтра в json у меня есть следующий sql
ISJSON(Properties) gt; 0 AND JSON_VALUE(Properties, '$.aKey') = 'AValue)
Поскольку запрос является динамическим, я могу получить запрос, который выглядит следующим образом
SELECT Id, Properties FROM [RecommendTest].[dbo].[TRecommendation2] WHERE (ISJSON(Properties) gt; 0 AND JSON_VALUE(Properties, '$._imps') = 'true') OR (ISJSON(Properties) gt; 0 AND JSON_VALUE(Properties, '$._imps') = 'false')
В SSMS это возвращает правильные результаты, но также выдает сообщение, если у меня есть строка в базе данных, содержащая недопустимый json в поле свойств. Сообщение заключается в следующем
Msg 13609, Уровень 16, Состояние 2, строка 2 Текст JSON неправильно отформатирован. Неожиданный символ » о » находится в позиции 1.
Это проблема, потому что я на самом деле использую это в EFCore, и возникает исключение.
Интересно, если я сделаю это (логически то же самое)
SELECT Id, Properties FROM [RecommendTest].[dbo].[TRecommendation2] WHERE ISJSON(Properties) gt; 0 AND ( JSON_VALUE(Properties, '$._imps') = 'true' OR JSON_VALUE(Properties, '$._imps') = 'false' )
Я получаю те же результаты, но не получаю сообщения
Я предполагаю, что это странность оптимизатора запросов… есть какие-нибудь идеи?
Комментарии:
1. Проблема, с которой вы сталкиваетесь, заключается в том, что ничто не мешает механизму обработки данных разрешить содержащееся
JSON_VALUE
в нем предложение, прежде чем оно разрешитISJSON
его. В результате, если JSON неверен иJSON_VALUE
сначала будет разрешен, вы получите сообщение об ошибке. В идеале, почему бы в первую очередь не остановить вставку недопустимого JSON в базу данных? Вы могли бы проверить значение как на уровне приложения, так и на уровне базы данных (с помощью aCONSTRAINT
), если бы действительно хотели. Тогда вам не нужно беспокоиться о том , чтобы проверить, действительно ли оно или нетWHERE
, так как оно всегда будет действительным.2. Вам нужно заставить оптимизатор оценить
isjson
первый, вы можете попробовать подзапрос или CTE, возможно, с целью строки или оценить его с помощьюapply
3. Рассмотрите ВОЗМОЖНОСТЬ ПЕРЕКРЕСТНОГО ПРИМЕНЕНИЯ. Вы можете складывать и ссылаться на вычисленные значения по мере необходимости с помощью данного псевдонима.
4.
JSON_VALUE(case when isjson(Properties)=1 then Properties end, '$._imps') in ('true', 'false')