postgresql select lines in json array with LIKE operator

Question

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 ?

This would be extremely simple if you used a normalized data model. — user330315
– user330315, Commented Oct 10, 2016 at 14:28

Laurenz Albe · Accepted Answer · 2016-10-10 14:27:16Z

2

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).

answered Oct 10, 2016 at 14:27

Laurenz Albe

257k22 gold badges314 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgresql select lines in json array with LIKE operator

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related