jedema jedema - 1 month ago 8
JSON Question

postgresql select lines in json array with LIKE operator

I have one table user with one JSON field user_data.
Example these 3 lines :

{"name": "John", domain_names: ['john.com', 'lennon.com', 'john.fr']}
{"name": "Foo", domain_names: ['foo.com', 'bar.fr', 'bar.com']}
{"name": "Tirion", domain_names: ['tirion.co.uk']}


I want to get JSON lines with one or more .com domaine name (The first two lines).
I tried this :

SELECT user_data
FROM user
WHERE json_array_elements(user_data)->>'domain_names' LIKE '%.com';


but it doesn't work...
Do you know a way to get lines with .com domain ?

Answer
SELECT user_data
FROM "user" u
WHERE EXISTS
         (SELECT 1
          FROM jsonb_array_elements_text(u.user_data->'domain_names') x(dom)
          WHERE dom LIKE '%.com');

By the way, you should avoid naming tables like reserved words (in that case user).

Comments