PostgreSQL Question posted in
The official documentation can be found here.

How find one JSONB item from JSONB array in PostgreSQL

I have this table:

id period_type period commission
123456 Daily 10 10
123456 Monthly 3 11
123456 Daily 5 12
123456 Monthly 1 13
123457 Monthly 6 14
123457 Daily 5 15
123457 Monthly 9 16

(Period type is int 1&2, but for example I wrote it by text)

I need get records with id and single jsonb with period_type, period and commission column. This jsonb must have max period with max period type. For example, id – 123456, period_type – ‘Monthly’, period – 3, commission – 11.

I know how get array of periods (jsonb_build_object and jsonb_agg), but don’t know how get 1 period with such conditions.

Tags:

2

Answers


  1. 0

    Next query can be written easier, but I am always seem to have problem when multiple MAX values pop-up in 1 query (like getting the commission for the max period for the max period_type):

      SELECT
    m3.id,
    m3.period_type,
    m3.period,
    m4.commission
  FROM (
    SELECT 
      m.id,
      m.period_type,
      max(m.period) as period
    FROM mytable m
    INNER JOIN (
      SELECT DISTINCT
        id,
        max(period_type) over (partition by id ) period_type
      FROM mytable
      ) x on x.id=m.id and x.period_type=m.period_type
    GROUP BY m.id, m.period_type
  ) m3
  INNER JOIN mytable m4 on m4.id=m3.id 
                       and m4.period_type=m3.period_type 
                       and m4.period=m3.period

    it retruns:

    id period_type period commission
    123456 Monthly 3 11
    123457 Monthly 9 16

    This query can be used as a sub-query to get JSON:

    SELECT json_agg(m5) 
from (
  SELECT
    m3.id,
    m3.period_type,
    m3.period,
    m4.commission
  FROM (
    SELECT 
      m.id,
      m.period_type,
      max(m.period) as period
    FROM mytable m
    INNER JOIN (
      SELECT DISTINCT
        id,
        max(period_type) over (partition by id ) period_type
      FROM mytable
      ) x on x.id=m.id and x.period_type=m.period_type
    GROUP BY m.id, m.period_type
  ) m3
  INNER JOIN mytable m4 on m4.id=m3.id 
                       and m4.period_type=m3.period_type 
                       and m4.period=m3.period
) m5

    output:

    [{"id":123456,"period_type":"Monthly","period":3,"commission":11}, 
 {"id":123457,"period_type":"Monthly","period":9,"commission":16}]

    see: DBFIDDLE

    Login or Signup to reply.
  2. 0

    This is a standard Top-1-Per-Group problem. Use ROW_NUMBER to get the top value in each group, then jsonb_agg to aggregate it up into a JSON array.

    SELECT jsonb_agg(m) 
FROM (
    SELECT
      m.id,
      m.period_type,
      m.period,
      m.commission
    FROM (
        SELECT 
          m.*,
          ROW_NUMBER() OVER (PARTITION BY id ORDER BY period_type DESC, period DESC) AS rn
        FROM mytable m
    ) m
    WHERE rn = 1
) m;

    db<>fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search