Запрос Postgre с JSONField на массиве
Я новичок в Postgres, и пытаюсь построить SQL запрос, который может получить словарную пару ключ/значение в массиве [] из таблицы table_b и использовать ее в WHERE предложении для поиска совпадений tag_name и tag_value, возвращая object_uuid
Оригинальные tags на table_b хранились как JSONField() в Django -> Postgres и не уверен, как это будет работать в массиве при извлечении каждого из них.
Вопрос: Как построить SQL-запрос, который может обойти каждый name и value в table_b.tags, а затем использовать его для сопоставления с table_a?
table_a
| tag_name | tag_value | object_uuid |
|---|---|---|
| foobar | coffee | aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee |
| hello | world | 3dd98cb6-978c-44b0-92fd-403032a7cb1f |
| key_one | 81bba637-4156-42b2-a2c0-ae5dd23ed695 |
table_b
| id | object_uuid | tags |
|---|---|---|
| 3 | 00000000-1111-2222-3333-444444444444 | |
| 4 | 99999999-8888-7777-6666-555555555555 | |
| 271 | [{"name": "foobar", "value": "coffee"}, {"name": "hello", "value": "world"}] |
Вы можете присоединиться с помощью оператора @> после построения массива с именем tagname и значением:
select b.id, b.object_uuid, a.tag_name, a.tag_value
from table_b b
join table_a a on b.tags @> jsonb_build_array(jsonb_build_object('name', a.tag_name, 'value', a.tag_value));
Это предполагает, что table_b.tags является колонкой jsonb (что на самом деле так и должно быть). Если это не так, необходимо привести его к виду b.tags::jsonb