I have 2 queries that separately run perfectly:
This one limits the results:
x
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.