Analysing and formatting JSON using PostgreSQL

Question

I have a table called api_details where i dump the below JSON value into the JSON column raw_data. Now i need to make a report from this JSON string and the expected output is something like below,

action_name.              sent_timestamp                           Sent. Delivered
campaign_2475             1600416865.928737 - 1601788183.440805.    7504. 7483
campaign_d_1084_SUN15_ex  1604220248.153903 - 1604222469.087918.   63095. 62961

Below is the sample JSON OUTPUT

{
  "header": [
    "#0 action_name",
    "#1 sent_timestamp",
    "#0 Sent",
    "#1 Delivered"
    
  ],
  "name": "campaign - lifetime",
  "rows": [
    [
      "campaign_2475",
      "1600416865.928737 - 1601788183.440805",
      7504,
      7483
    ],
    [
      "campaign_d_1084_SUN15_ex",
      "1604220248.153903 - 1604222469.087918",
      63095,
      62961
    ],
    [
      "campaign_SUN15",
      "1604222469.148829 - 1604411016.029794",
      63303,
      63211 
    ]
  ],
  "success": true
}

I tried like below, but is not getting the results.I can do it using python by lopping through all the elements in row list.

But is there an easy solution in PostgreSQL(version 11).

SELECT raw_data->'rows'->0 
  FROM api_details

Barbaros Özhan · Accepted Answer · 2020-11-04 15:10:12Z

3

You can use JSONB_ARRAY_ELEMENTS() function such as

SELECT (j.value)->>0 AS action_name,
       (j.value)->>1 AS sent_timestamp,
       (j.value)->>2 AS Sent,
       (j.value)->>3 AS Delivered
  FROM api_details
 CROSS JOIN JSONB_ARRAY_ELEMENTS(raw_data->'rows') AS j

Demo

P.S. in this case the data type of raw_data is assumed to be JSONB, otherwise the argument within the function raw_data->'rows' should be replaced with raw_data::JSONB->'rows' in order to perform explicit type casting.

edited Nov 4, 2020 at 15:10

answered Nov 3, 2020 at 22:10

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

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

3 Comments

Linu Over a year ago

Got this error ERROR: function jsonb_array_elements(json) does not exist LINE 7: CROSS JOIN JSONB_ARRAY_ELEMENTS(raw_data->'rows') AS j ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SQL state: 42883 Character: 229

Barbaros Özhan Over a year ago

Hi @Linu , seems that you need to replace JSONB_ARRAY_ELEMENTS(raw_data->'rows') with JSONB_ARRAY_ELEMENTS(raw_data::JSONB->'rows') , e.g. raw_data is not of type JSON(B) in your case(probably a of a TEXT type), while in our demo in the answer it's of type JSONB. Consider this new Demo for your case.

Bergi Over a year ago

The casts of j.value to jsonb should not be necessary

Collectives™ on Stack Overflow

Analysing and formatting JSON using PostgreSQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related