Add a new row for each jsonb value pairs



  • I have this table from were I'm running a query:

    CREATE TABLE IF NOT EXISTS public.preprocess_things
    (
        preprocess_id integer NOT NULL DEFAULT nextval('preprocess_things_preprocess_id_seq'::regclass),
        arrive_date date NOT NULL,
        arrive_location character varying COLLATE pg_catalog."default" NOT NULL,
        data jsonb NOT NULL,
        CONSTRAINT preprocess_things_pkey PRIMARY KEY (preprocess_id),
        CONSTRAINT preprocess_things_arrive_date_arrive_location_bo_key UNIQUE (arrive_date, arrive_location)
    )
    

    The query that I'm running is this:

    SELECT DATE_TRUNC('month', arrive_date) AS grouped_date,
      LOWER(arrive_location) AS location,
      json_build_object(
        '1', SUM((data->'1')::int),
        '2', SUM((data->'2')::int),
        '3', SUM((data->'3')::int),
        '4', SUM((data->'4')::int),
        '5', SUM((data->'5')::int)
        ) AS data
    FROM preprocess_things
    GROUP BY grouped_date,
      location
    

    The current result is:

    grouped_date location data
    2018-06-01 00:00:00 location_00 {"1" : 1, "2" : null, "3" : null, "4" : 1, "5" : 8}
    2018-05-01 00:00:00 location_00 {"1" : null, "2" : 9, "3" : 10, "4" : 8, "5" : 3}

    I would like to apply another SELECT, that adds a row for each value pair that does not have a null value, where the key goes to the thing_type column and the value goes to the total column; like this:

    grouped_date location thing_type total
    2018-06-01 00:00:00 location_00 1 1
    2018-06-01 00:00:00 location_00 4 1
    2018-06-01 00:00:00 location_00 5 8
    2018-05-01 00:00:00 location_00 2 9
    2018-05-01 00:00:00 location_00 3 10
    2018-05-01 00:00:00 location_00 4 8
    2018-05-01 00:00:00 location_00 5 3

    The fiddle db can be found https://dbfiddle.uk/?rdbms=postgres_13&fiddle=6524fe338ae6bde54fe9bd100e11c785 .

    UPDATE:

    Thanks to https://dba.stackexchange.com/users/143823/gerard-h-pille , I tweak a little bit he's response to this:

    SELECT
      DATE_TRUNC('month', arrive_date) AS grouped_date,
      LOWER(arrive_location) AS location,
      x.key::int thing_type, sum(x.value::int) total
    FROM preprocess_things
    JOIN  jsonb_each(data) x ON (x.key::int = ANY('{1,2,3}'::int[]))
    GROUP BY grouped_date,  location, x.key::int
    ORDER BY grouped_date,  location, x.key::int;
    

    Since there are some cases were I just need a selection of the thing_type and not all of them to be calculated, the only problem is by adding the JOIN and compare the query speed is not that good.



  • SELECT
      DATE_TRUNC('month', arrive_date) AS grouped_date,
      LOWER(arrive_location) AS location,
      x.key::int  thing_type, sum(x.value::int) total
        FROM preprocess_things,
             jsonb_each(data) x
      GROUP BY grouped_date,  location, x.key::int
      order by grouped_date,  location, x.key::int;
    

    see https://dbfiddle.uk/?rdbms=postgres_13&fiddle=30dcd885d5581ab83c8f23602cba4b9c




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2