Search code examples
sqlarraysjsonpostgresqljsonb

LIMIT and Contains in a single JSONB Query

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.

Solution

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