I have 2 queries that separately run perfectly:
This one limits the results:
SELECT jsonb_agg(elem) as data FROM ( SELECT * FROM file_data, jsonb_array_elements(file_data) a(elem) LIMIT 3 ) sub;
This one correctly runs a ‘contains’ filter:
SELECT jsonb_agg(obj) FROM file_data, jsonb_array_elements(file_data) obj WHERE obj->>'first_name' LIKE '%M%';
How do I combine these so that I can “select data where the first name contains M, and limit the result to X rows”?
Here is a SQLFiddle with fake data to test it on. I am unsure how to proceed.
Advertisement
Answer
Use limit
a subquery before aggregating:
select jsonb_agg(obj) from ( select obj from file_data, jsonb_array_elements(file_data) obj where obj->>'first_name' LIKE '%M%' limit 3 ) x ;
Note: you probably want to add an order by
clause in the subquery; without it, it is not possible to predict which 3 records will be picked.