Postgresql 10 jsonb to table with multiple rows

Question

I have a a json array that is containing some "users" structure. These json arrays are stroe in a Postgresql database and I would like to get all lines (for all rows for all arrays).

Data sample:

My database instance:

docker run --name postgresql-10 -e POSTGRES_PASSWORD=mysecretpassword -d postgres:10-alpine 

docker run -it --rm --link postgresql-10:postgres postgres:10-alpine psql -h postgres -U postgres

My table:

CREATE TABLE "Reports" (
    "name" TEXT NOT NULL,
    "report" JSONB NOT NULL,
    "timestamp" TIMESTAMP NOT NULL,
    PRIMARY KEY ("name", "timestamp")
)
;

Some data:

insert into "Reports" (timestamp, "name", "report")
  values ('2017-11-05'::timestamp, 
          'appA', 
          '{
         "dateComputed": "2017-11-06 10:06:29 UTC",
         "name": "appA",
         "users": [
           {
             "DATE": "2017-11-03",
             "EMPLID": "415",
             "NAME": "Smith"
               },
               {
             "DATE": "2017-11-03",
         "EMPLID": "4",
         "NAME": "Jane"
           },
           {
             "DATE": "2017-11-03",
             "EMPLID": "31",
             "NAME": "Doe"
           }
         ]
      }'::jsonb
         ) ;

insert into "Reports" (timestamp, "name", "report")
  values ('2017-11-04'::timestamp, 
          'appA', 
          '{
         "dateComputed": "2017-11-04 11:34:13 UTC",
         "name": "appA",
         "users": [
               {
             "DATE": "2017-11-03",
         "EMPLID": "4",
         "NAME": "Jane"
           },
           {
             "DATE": "2017-11-03",
             "EMPLID": "31",
             "NAME": "Doe"
           }
         ]
      }'::jsonb
         ) ;

insert into "Reports" (timestamp, "name", "report")
  values ('2017-11-01'::timestamp, 
          'appA', 
          '{
         "dateComputed": "2017-11-01 02:32:49 UTC",
         "name": "appA",
         "users": [
               {
             "DATE": "2017-11-01",
         "EMPLID": "415",
         "NAME": "Smith"
           },
           {
             "DATE": "2017-11-01",
             "EMPLID": "31",
             "NAME": "Doe"
           }
         ]
      }'::jsonb
         ) ;


insert into "Reports" (timestamp, "name", "report")
  values ('2017-11-03'::timestamp, 'appB', '[{"other": "useless"}]'::jsonb) ;

What I would like is following table listing all users matching "Reports" whose "name" is 'AppA' :

+------------+-------+--------+
| DATE       | NAME  | EMPLID |
+------------+-------+--------+
| 2017-11-03 | Smith | 415    |
+------------+-------+--------+
| 2017-11-03 | Jane  | 4      |
+------------+-------+--------+
| 2017-11-03 | Doe   | 31     |
+------------+-------+--------+
| 2017-11-03 | Jane  | 4      |
+------------+-------+--------+
| 2017-11-03 | Doe   | 31     |
+------------+-------+--------+
| 2017-11-01 | Smith | 415    |
+------------+-------+--------+
| 2017-11-01 | Doe   | 31     |
+------------+-------+--------+

+------------+------------+-------+--------+
| timestamp  | DATE       | NAME  | EMPLID |
+------------+------------+-------+--------+
| 2017-11-05 | 2017-11-03 | Smith | 415    |
+------------+------------+-------+--------+
| 2017-11-05 | 2017-11-03 | Jane  | 4      |
+------------+------------+-------+--------+
| 2017-11-05 | 2017-11-03 | Doe   | 31     |
+------------+------------+-------+--------+
| 2017-11-04 | 2017-11-03 | Jane  | 4      |
+------------+------------+-------+--------+
| 2017-11-04 | 2017-11-03 | Doe   | 31     |
+------------+------------+-------+--------+
| 2017-11-03 | 2017-11-01 | Smith | 415    |
+------------+------------+-------+--------+
| 2017-11-03 | 2017-11-01 | Doe   | 31     |
+------------+------------+-------+--------+

When I am only matching a single row then I am able to use jsonb_to_recordset to get all json lines matching that row. For instance when filtering on latest timestamp column by creating a view:

CREATE INDEX "ReportsGIN" on "Reports" USING gin ("report") ;

CREATE VIEW "Reports_Latest_timestamp"
AS
SELECT  "name"
       , max("Reports"."timestamp") AS "timestamp_latest"
FROM "Reports"
GROUP BY "name"
;

CREATE VIEW "Reports_Latest"
AS
SELECT   "Reports"."name"
       , "Reports"."report"
       , "Reports"."timestamp"
FROM "Reports"
WHERE ("Reports"."timestamp" = (SELECT "Reports_Latest_timestamp"."timestamp_latest" FROM "Reports_Latest_timestamp" WHERE "Reports_Latest_timestamp"."name" = "Reports"."name"))
;

select *
from
jsonb_to_recordset
(
 (select report#>'{users}'
  from "Reports_Latest" 
  where "name" = 'appA' 
) 
) as x(
        "EMPLID" integer
      , "NAME" text
      , "DATE" timestamp with time zone

)
;

 EMPLID | NAME  |          DATE
--------+-------+------------------------
    415 | Smith | 2017-11-03 00:00:00+00
      4 | Jane  | 2017-11-03 00:00:00+00
     31 | Doe   | 2017-11-03 00:00:00+00
(3 rows)

jsonb_to_recordset is working as expected.

How can I use jsonb_to_recordset to list lines for all "reports" row?

An answer to display "timestamp" on "Reports_Latest" would be (but still no clue for full "Reports" lines):

select  t."timestamp"
      , r."EMPLID"
      , r."NAME"
      , r."DATE"
from
(
  select "timestamp", report#>'{users}'
  from "Reports_Latest" 
  where "name" = 'appA' 
) as t
, (
  select *
  from
    jsonb_to_recordset
    (
     (select report#>'{users}'
      from "Reports_Latest" 
      where "name" = 'appA' 
    ) 
    ) as x(
            "EMPLID" integer
          , "NAME" text
          , "DATE" timestamp with time zone

    )
  ) as r
;


      timestamp      | EMPLID | NAME  |          DATE
---------------------+--------+-------+------------------------
 2017-11-05 00:00:00 |    415 | Smith | 2017-11-03 00:00:00+00
 2017-11-05 00:00:00 |      4 | Jane  | 2017-11-03 00:00:00+00
 2017-11-05 00:00:00 |     31 | Doe   | 2017-11-03 00:00:00+00
(3 rows)

SQL Fiddle on Postgresql 9.6 to quick test

The solution provided by Breathe is:

select r."timestamp", x.*
from "Reports" as r
cross join lateral jsonb_to_recordset (r.report#>'{users}')
 as x(
        "EMPLID" integer
      , "NAME" text
      , "DATE" timestamp with time zone

)
where r."name" = 'appA' 
;

      timestamp      | EMPLID | NAME  |          DATE
---------------------+--------+-------+------------------------
 2017-11-05 00:00:00 |    415 | Smith | 2017-11-03 00:00:00+00
 2017-11-05 00:00:00 |      4 | Jane  | 2017-11-03 00:00:00+00
 2017-11-05 00:00:00 |     31 | Doe   | 2017-11-03 00:00:00+00
 2017-11-04 00:00:00 |      4 | Jane  | 2017-11-03 00:00:00+00
 2017-11-04 00:00:00 |     31 | Doe   | 2017-11-03 00:00:00+00
 2017-11-01 00:00:00 |    415 | Smith | 2017-11-01 00:00:00+00
 2017-11-01 00:00:00 |     31 | Doe   | 2017-11-01 00:00:00+00
(7 rows)

http://sqlfiddle.com/#!17/cd4df/9/0

Breathe · Accepted Answer · 2017-11-08 13:02:57Z

2

Essentially, what you are trying to do is to generate as many record as there are users in one single row of "Reports". In your desired table structure, the first two columns are the first two columns from "Reports". So your query needs:

select a.timestamp, a."name"
FROM "Reports" a

Then you want to create a "subset" of records for each of these. This is acheivable by applying the functiont that generate the subset to litterally all the rows. The subset is generated by the function jsonb_to_recordset(), therefore :

SELECT a.timestamp, a."name", b. *
FROM "Reports" a
CROSS JOIN lateral jsonb_to_recordset(a.report->'Users')
as b("EMPLID" integer
      , "NAME" text
      , "DATE" timestamp with time zone)

EDIT: I added the cross join lateral

edited Nov 8, 2017 at 13:02

answered Nov 7, 2017 at 13:18

Breathe

7247 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Jackson Brody Over a year ago

I would like to get all lines from "Reports" and not only from "Reports_Latest"

dpr Over a year ago

While this code snippet may solve the question, including an explanation really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion.

Jackson Brody Over a year ago

I have removed the ",b. *". Error displayed is : ERROR: syntax error at or near ";" LINE 7: ; ^

Breathe Over a year ago

can you try: SELECT * from Reports" a LEFT JOIN jsonb_to_recordset(a.report->'Users') as x("EMPLID" varchar, "NAME" varchar, "DATE" varchar)

Jackson Brody Over a year ago

postgres=# SELECT * from "Reports" a LEFT JOIN jsonb_to_recordset(a.report->'Users') as x("EMPLID" varchar, "NAME" varchar, "DATE" varchar) postgres-# ; ERROR:  syntax error at or near ";" LINE 2: ;         ^

|

Collectives™ on Stack Overflow

Postgresql 10 jsonb to table with multiple rows

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related