Важно! Postgresql v 9.3
в данной версии нет типа и функций для jsonb

Таблица:

CREATE TABLE aa_ym_test (
  id serial primary key,
  cl_json json
);

Данные:

insert into aa_ym_test (cl_json) values 
('{"uniqueId":"15036812687876940861125AT","fileName":"ym-test-010.jpg","categories":[{"type":"directory","titles":[],& ; ;quo t;directories":[],"path":"YM/images"}]}'),
('{"uniqueId":"15036812687855470861125AT","fileName":"ym-test-010.jpg","categories":[{"type":"asset","titles":[],"di   rectories":[],"path":"YM/images"}]}'),
('{"uniqueId":"15036812687888870861125AT","fileName":"ym-test-010.jpg","categories":[{"type":"directory","titles":[],& ; ;quo t;directories":[],"path":"YM/video"}]}'),
('{"uniqueId":"15036812687446820861125AT","fileName":"ym-test-010.jpg","categories":[]}')

Нужно получить все id, для которых:
- categories->path == 'YM/images'
и
- categories->type == 'directory'

В SELECT-те все нормально

select id, json_array_elements(t.cl_json->'categories')->'path', json_array_elements(t.cl_json->'categories')->'type' from aa_ym_test t 

1	"YM/images"	"directory"
2	"YM/images"	"asset"
3	"YM/video"	"directory"

Но в WHERE работать не хочет.
ни так:

select id from aa_ym_test where json_array_elements(cl_json->'categories')->'path' = '{"YM/images"}' and json_array_elements(cl_json->'categories')->'type' = '{"directory"}'

ни так:

select id from aa_ym_test where json_array_elements(cl_json->'categories')->'path'::text = '"YM/images"' and json_array_elements(cl_json->'categories')->'type'::text = '"directory"'

В обоих случаях одна и та же ошибка: Operator does not exist: json = unknown

sergeiss ?
:rolleyes:

_____________
[продано копирайтерам]