0

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

0

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.

1
  • Hi Sahap, thanks for the solution. This works in postgresql version 12. My postgres db version is 11. and it gives me errors for the jsonb_path_query_first() function. – Emrullah Apr 19 '20 at 23:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.