PostgreSQL имеет тип данных Jsonb, который позволяет добавлять к стандартной реляционной модели дополнительные свойства с возможностью поиска по ним.
EntityFramework Core с расширением Npgsql умеет вытягивать данные поля в тип System.String
Однако для фильтрации по Json свойствам через EF на уровне запросов приходится использовать чистый SQL, что не очень то удобно, так как нужно лезть в маппинг (если он не автоматический), искать названия полей, соответствующих свойствам моделей, поддерживать это именование. Пропадает гибкость, которую нам дает ORM.
Если вас это угнетает, так же как и меня, добро пожаловать под кат.
В конце статьи имеется ссылка на исходники!
Обозначим задачи
Я, как разработчик, хочу иметь инструмент для доступа к полям Jsonb с целью фильтрации и сортировки по ним, который:
- Будет совместим с EntityFramework Core 2 (мы используем именно его)
- Не потребует во время работы с ним самому писать SQL
- Будет работать с плоской структурой Json (внутри json есть только json свойства)
Добавлю еще, что есть Npgsql.Json.NET, который умеет проецировать Json и Jsonb значения в CLR типы. Если честно, не понимаю, для чего он может понадобиться, ведь раз у нас появилась необходимость в Json поле в реляционной БД, вероятнее всего у нас есть сущности с динамическим набором полей.
Алгоритм решения задачи
- Определить метод (или методы), который будет покрывать наши потребности.
- Создать транслятор, который будет участвовать в генерации SQL кода.
- Прикрутить все это к Npgsql.
Решение
Для начала определим метод. Хочу, чтобы он использовался как то так:
context.Entity.Where(x => JsonbMethods.Value<string>(x.JsonbField, "jsonPropertyName") == "value")Следовательно, вот наш метод:
public static TSource Value<TSource>(object jsonbProperty, string jsonbPropertyName)
{
throw new NotSupportedException();
}Я несколько часов ковырял исходники EF Core, Npgsql и не только в поисках способов расширить базовый функционал генерации SQL. Добрался вот до этой статьи, но подход автора по способу подключения транслятора мне не понравился, ведь он переопределяет стандартный инструмент, а значит может конфликтовать с другим похожим инструментом.
В итоге добрался до исходников Net Topology Suite. Все, что мне оттуда потребовалось, это способ подключения транслятора методов.
Но больше всего времени я потратил на то, чтобы сформировать нужный мне фрагмент sql.
Вот требуемый синтаксис
tableAlias."JsonField"->>"insideProperty"
Сначала я пробовал в трансляторе возвращать ColumnExpression. Первым параметром при его создании идет имя столбца (string). Я просто состряпал его из параметров, которые мне приходят в метод. Запустил, проверил, ошибка. Оказывается, то, что я передаю в качестве имени, оборачивается в кавычки. В итоге SQLполучился таким tableAlias.""JsonField"->>"insideProperty"".
В исходниках генератора я нашел метод VisitColumn, в котором это поведение было хардкодным и не зависело ни от каких параметров. То есть я не мог на это повлиять. Нужно было искать другое решение.
Тогда я создал собственный Expression — JsonbPropertyAccessorExpression: Expression
Осталось переопределить его метод Accept для ISqlExpressionVisitor.
Но вот беда, в данном интерфейсе нет метода, который бы мог сегнерировать кастомный оператор. Тогда мне пришла в голову мысль посетить не один метод, а несколько. Посетил сначала VisitColumn, который создал доступ к столбцу tableAlias.«JsonField», затем VisitSqlFragment, в который я прокинул "->>'insideFieldName'".
Я и не надеялся, но все заработало. Почти.
Когда я пытался фильтровать по тексту по точному совпадению почему то формировался такой фильтр tableAlias."JsonField"->>"insideProperty" = JSONB "value", что вызывало ошибку, так как привести текст к типу JSONB нельзя, если там не содержится валидный Json. Да и зачем мне что-то к чему-то приводить, когда я хочу текст?
Я было даже принял решение из маппинга модели убрать пометку со столбца Jsonb, что это Jsonb, добавив только эту пометку в MigrationContext, чтобы он генерировал правильные миграции. И это даже взлетело, но подход показался мне костыльным. Тем не менее на этом я и остановился.
После этого я принялся за CAST, ведь метод Value у меня универсальный и в Json свойствах могут быть различные типы данных, по которым тоже нужно сортировать и фильтровать.
В итоге из моего транслятора я стал возвращать ExplicitCastExpression, в который передавал свой кастомный Expression и тип, который содержался в универсальных аргументах метода Value.
когда я посмотрел на получившийся SQL при поиске по дате, я обнаружил, что к сравниваемое значение приводится к типу timestamp. timestamp 'some date value'. И тут до меня дошло. Предыдущая проблема, которую я решил костылем, ушла сама собой. Когда аксессор к полю Json кастился в текст, генератор больше не добавлял явное преобразование в JSONB, ведь слева операции сравнения уже был текст, а по умолчанию аксессор поля Jsonb возвращает тип Jsonb.
В завершении
В завершении хочу добавить, что я не нашел документации по тому, как мне добавить кастомные трансляторы свойств и методов. наверное, плохо искал. Если у кого-то есть замечания по подходу, по коду и тд, пишите в комментариях.
Если кто-нибудь захочет расширить библиотеку в форках, пишите в личку, я постараюсь помочь. Ну или кидайте пулреквесты.
