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.

• Emrullah: 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.