PostgreSQL query and shape in json array? 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-&gt;&gt;'cpc' as CPC,
   jdata-&gt;&gt;'ad_name' as ad_name,
   jdata-&gt;&gt;'cpm' as CPM,
   jdata-&gt;&gt;'cpp' as CPP,
   jdata-&gt;&gt;'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-&gt;video_view  |actions-&gt;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
You can use jsonb_path_query_first function. Here is a sample usage.


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


ad_name      | actions-&gt;video_view | actions-&gt;mobile_app_install
:----------- | :------------------ | :--------------------------
ad name here | &quot;11&quot;                | &quot;33&quot;                       


db&lt;&gt;fiddle here
You can read The SQL/JSON Path Language for reference.