PostgreSQL query and shape in json array?

Question

I have a json array stored in my postgres database in jsonb column. The json data is facebook ad data and look like this:

{
"cpc": "1.1",
"cpm": "11.1",
"cpp": "11.1",
"ctr": "0.11",
"ad_id": "11",
"reach": "1",
"spend": "1.1",
"clicks": "11",
"actions": [
    {
        "value": "11",
        "action_type": "video_view"
    },
    {
        "value": "1",
        "action_type": "post_reaction"
    },
    {
        "value": "11",
        "action_type": "mobile_app_install"
    }
],
"ad_name": "ad name here",
"adset_id": "11111",
"date_stop": "2015-05-25",
"frequency": "1.0511",
"adset_name": "adset name here",
"date_start": "2015-05-20",
"campaign_id": "11111111",
"impressions": "1111",
"account_name": "Account name here",
"campaign_name": "campaign_name here",
"unique_clicks": "11",
"unique_actions": [
    {
        "value": "111",
        "action_type": "video_view"
    },
    {
        "value": "11",
        "action_type": "post_reaction"
    },
    {
        "value": "11",
        "action_type": "mobile_app_install"
    },
    {
        "value": "11",
        "action_type": "link_click"
    }
],
"publisher_platform": "instagram",
"inline_link_click_ctr": "0.1111"

}

Now, I can select the firt elements like:

select jdata->>'cpc' as CPC,
   jdata->>'ad_name' as ad_name,
   jdata->>'cpm' as CPM,
   jdata->>'cpp' as CPP,
   jdata->>'actions' as actions
from my_table_name

I want to have row data by selecting and it returns similiar kind that I need. But for actions element not. Because "actions" element is a json array. I need the "actions" element like json to row but like this format;

---------------------------------------------------------------------
| ad_name        |actions->video_view  |actions->mobile_app_install |
---------------------------------------------------------------------
| ad name here   |         11          |            1000            |
---------------------------------------------------------------------
| 2 ad name here |         12          |            10001           |
---------------------------------------------------------------------

I have queried with jsonb_array_elements function, but it did not return that i need?

Any help and suggestion appreciated

Answer 1

You can use jsonb_path_query_first function. Here is a sample usage.

SELECT
    jdata->>'ad_name' as ad_name,
    jsonb_path_query_first(
      jdata , 
    '$.actions[*] ? (@.action_type == "video_view").value'
    ) AS "actions->video_view",
    jsonb_path_query_first(
      jdata , 
    '$.actions[*] ? (@.action_type == "mobile_app_install").value'
    ) AS "actions->mobile_app_install"
  FROM test

ad_name      | actions->video_view | actions->mobile_app_install
:----------- | :------------------ | :--------------------------
ad name here | "11"                | "33"                       

db<>fiddle here

You can read The SQL/JSON Path Language for reference.