Advertisement
Guest User

Get value from from JSONB with top-level array by pattern

a guest
Aug 16th, 2018
72
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 0.33 KB | None | 0 0
  1. WITH  temp (name, aliases) AS (VALUES (1, '[{"value":"test"}, {"value":"wrong"}]'::jsonb), (2, '[{"value":"another"}, {"value":"wrong"}]'::jsonb), (3, '[{"value":"another"}, {"value":"test"}]'::jsonb))
  2. SELECT * FROM temp
  3. WHERE EXISTS (
  4.     SELECT 1 FROM jsonb_array_elements(aliases) AS j(DATA) WHERE (DATA #>> '{value}') ilike '%es%');
  5. )
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement