jedema jedema - 1 year ago 83
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: ['', '', '']}
{"name": "Foo", domain_names: ['', '', '']}
{"name": "Tirion", domain_names: ['']}

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 '';

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

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

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download