CODEWITHSUNDEEP
sqlarraysjsonpostgresqlcount
mike hennessy
mike hennessy

Reputation: 1679

Can I combine 2 Json_agg queries into a single result?

I have the following 2 queries. I need to use two queries, as the first query gets the total matching records, and the second query returns the first 50 results. I'd like to run both queries in a single call and have the result combined into a single result?

How could I combine these two results into a single value? I've tried UNION but that doesn't seem to work with JSON output.

I'm using Postges 11

    select count(tbl_310.id)
    FROM 
    schema_1.tbl_310 tbl_310
    left join schema_1.tbl_310_customid custom on custom.id=tbl_310.id
    left join lateral (
          select distinct field_53.*
          from  schema_1.tbl_310_to_tbl_342_field_53 field_53
          where field_53.tbl_310_id=tbl_310.id
          limit 50) field_53 on true

          left join lateral (
              select distinct tbl_342.*
              from schema_1.tbl_342_customid tbl_342
              where tbl_342.id = field_53.tbl_342_id
              limit 50
          ) tbl_342 on true

     left join lateral (
      select distinct field_41_join.*
      from  schema_1.tbl_310_field_41_join field_41_join
      where field_41_join.id=tbl_310.id
      limit 50) field_41_join on true

      left join lateral (
          select distinct field_41.*
          from schema_1.tbl_310_field_41 field_41
          where field_41.optionid = field_41_join.optionid
          limit 5
      ) field_41 on true left join lateral (
      select distinct field_52_join.*
      from  schema_1.tbl_310_field_52_join field_52_join
      where field_52_join.id=tbl_310.id
      limit 50) field_52_join on true

      left join lateral (
          select distinct field_52.*
          from schema_1.tbl_310_field_52 field_52
          where field_52.optionid = field_52_join.optionid
          limit 5
      ) field_52 on true 
      WHERE 
      field_52.optionid = ANY (Array[1]) ;


  select array_to_json(array_agg(t)) from (
  select
  tbl_310.id,custom.fullname as "CustomID",tbl_310.field_42,tbl_310.field_16,tbl_310.field_1,tbl_310.field_2,tbl_310.field_44,tbl_310.field_8,tbl_310.field_45,tbl_310.id
  ,jsonb_agg(distinct jsonb_build_object('id',tbl_342.id,'data',tbl_342.fullname)) as field_53
  , jsonb_agg(distinct jsonb_build_object('optionid',field_41.optionid,'data',field_41.option,'attributes',field_41.attributes)) as field_41,jsonb_agg(distinct jsonb_build_object('optionid',field_52.optionid,'data',field_52.option,'attributes',field_52.attributes)) as field_52

  FROM
  schema_1.tbl_310 tbl_310
  left join schema_1.tbl_310_customid custom on custom.id=tbl_310.id
  left join lateral (
          select distinct field_53.*
          from  schema_1.tbl_310_to_tbl_342_field_53 field_53
          where field_53.tbl_310_id=tbl_310.id
          limit 50) field_53 on true

          left join lateral (
              select distinct tbl_342.*
              from schema_1.tbl_342_customid tbl_342
              where tbl_342.id = field_53.tbl_342_id
              limit 50
          ) tbl_342 on true

  left join lateral (
      select distinct field_41_join.*
      from  schema_1.tbl_310_field_41_join field_41_join
      where field_41_join.id=tbl_310.id
      limit 50) field_41_join on true

      left join lateral (
          select distinct field_41.*
          from schema_1.tbl_310_field_41 field_41
          where field_41.optionid = field_41_join.optionid
          limit 5
      ) field_41 on true left join lateral (
      select distinct field_52_join.*
      from  schema_1.tbl_310_field_52_join field_52_join
      where field_52_join.id=tbl_310.id
      limit 50) field_52_join on true

      left join lateral (
          select distinct field_52.*
          from schema_1.tbl_310_field_52 field_52
          where field_52.optionid = field_52_join.optionid
          limit 5
      ) field_52 on true

      WHERE 
  field_52.optionid = ANY (Array[1]) 
      GROUP BY tbl_310.id,custom.fullname,tbl_310.field_42,tbl_310.field_16,tbl_310.field_1,tbl_310.field_2,tbl_310.field_44,tbl_310.field_8,tbl_310.field_45,tbl_310.id
      ORDER BY tbl_310.id asc
      Limit 50
      ) t;

UPDATED trying to use your format, but getting syntax error. See any issue here?

 SELECT
          count(tbl_310.id) AS count,
          array_to_json((array_agg(t))[1:50]) AS array
          FROM
          select
          tbl_310.id,custom.fullname as "CustomID",tbl_310.field_42,tbl_310.field_16,tbl_310.field_1,tbl_310.field_2,tbl_310.field_44,tbl_310.field_8,tbl_310.field_45,tbl_310.id
          ,jsonb_agg(distinct jsonb_build_object('id',tbl_342.id,'data',tbl_342.fullname)) as field_53
          , jsonb_agg(distinct jsonb_build_object('optionid',field_41.optionid,'data',field_41.option,'attributes',field_41.attributes)) as field_41,jsonb_agg(distinct jsonb_build_object('optionid',field_52.optionid,'data',field_52.option,'attributes',field_52.attributes)) as field_52
          FROM
          schema_1.tbl_310 tbl_310
          left join schema_1.tbl_310_customid custom on custom.id=tbl_310.id
          left join lateral (
                  select distinct field_53.*
                  from  schema_1.tbl_310_to_tbl_342_field_53 field_53
                  where field_53.tbl_310_id=tbl_310.id
                  limit 50) field_53 on true
 
                  left join lateral (
                      select distinct tbl_342.*
                      from schema_1.tbl_342_customid tbl_342
                      where tbl_342.id = field_53.tbl_342_id
                      limit 50
                  ) tbl_342 on true
 
          left join lateral (
              select distinct field_41_join.*
              from  schema_1.tbl_310_field_41_join field_41_join
              where field_41_join.id=tbl_310.id
              limit 50) field_41_join on true
 
              left join lateral (
                  select distinct field_41.*
                  from schema_1.tbl_310_field_41 field_41
                  where field_41.optionid = field_41_join.optionid
                  limit 5
              ) field_41 on true left join lateral (
              select distinct field_52_join.*
              from  schema_1.tbl_310_field_52_join field_52_join
              where field_52_join.id=tbl_310.id
              limit 50) field_52_join on true
 
              left join lateral (
                  select distinct field_52.*
                  from schema_1.tbl_310_field_52 field_52
                  where field_52.optionid = field_52_join.optionid
                  limit 5
              ) field_52 on true
 
                  WHERE 
              field_52.optionid = ANY (Array[1]) 
                  GROUP BY tbl_310.id,custom.fullname,tbl_310.field_42,tbl_310.field_16,tbl_310.field_1,tbl_310.field_2,tbl_310.field_44,tbl_310.field_8,tbl_310.field_45,tbl_310.id
                  ORDER BY tbl_310.id asc
                  ) t

Upvotes: 0

Views: 464

Answers (1)

Bergi
Bergi

Reputation: 664548

A CTE would help:

WITH (… long and complicated query …) AS intermediate
SELECT
  (SELECT count(*) FROM intermediate) AS count,
  (SELECT json_agg(t) FROM intermediate t LIMIT 50) AS array;

Alternatively, you can use the array that you're already building:

WITH (SELECT array_agg(t) AS arr FROM (… long and complicated query …) t) AS intermediate
SELECT
  array_length(arr, 1) AS count,
  array_to_json(arr[1:50]) AS array
FROM intermediate;

which might not even need the CTE syntax but only a subquery, or even just

SELECT
  count(*),
  array_to_json((array_agg(t))[1:50]) AS array
FROM (… long and complicated query …) t

Upvotes: 1

Related Questions