PostgreSQL query and shape in json array?
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.