问题

I have a JSON column that has multiple keys and each key has again key-value pairs in values.

and I need to change this to this in PostgreSQL.

I tried with json_array_elements but in the result, I'm still getting a JSON column for each key in a row, this gives duplicate rows wrt the number of keys I have in a JSON record.

SELECT *
FROM public.ga4 as a
CROSS JOIN json_array_elements(event_params::json) AS event_params
LIMIT 100

But I want only 1 row and key-value pairs in columns.

SELECT *
  FROM public.ga4 as a
 CROSS JOIN json_array_elements(event_params::json) AS event_params
 LIMIT 100

答案1

得分: 1

以下是翻译好的代码部分：

OK，你不打算将数据以文本形式分享，我们必须接受这一点。这对你的情况应该有效：

    with data as (select event_date, event_timestamp, event_name, key, value
                  from sampleData,
                       lateral jsonb_to_recordset(event_params) as x(key text, value jsonb))
    select event_date,
           event_timestamp,
           event_name,
           max(case when key = 'session_engaged' then value ->> 'string_value' end)         session_engaged,
           max(case when key = 'source' then value ->> 'string_value' end)                  source,
           max(case when key = 'engaged_session_event' then value ->> 'int_value' end::int) engaged_session_event
    from data
    group by event_date, event_timestamp, event_name;

这里是[DBFiddle演示][1]

编辑：如果你的版本允许（v12或更高版本），有一个更简单的版本：

    SELECT event_date, event_timestamp, event_name
         ,jsonb_path_query_first(event_params, '$[*] ? (@.key == "session_engaged")') #>> '{value,string_value}' as session_engaged
         ,jsonb_path_query_first(event_params, '$[*] ? (@.key == "source")') #>> '{value,string_value}' as source
         ,(jsonb_path_query_first(event_params, '$[*] ? (@.key == "engaged_session_event")') #>> '{value,int_value}')::int as engaged_session_event
    from sampleData;

这里是[DBFiddle演示][2]

[1]: https://dbfiddle.uk/I7CXFjzp
[2]: https://dbfiddle.uk/h1ZwV7sV

with data as (select event_date, event_timestamp, event_name, key, value
from sampleData,
lateral jsonb_to_recordset(event_params) as x(key text, value jsonb))
select event_date,
event_timestamp,
event_name,
max(case when key = 'session_engaged' then value ->> 'string_value' end)         session_engaged,
max(case when key = 'source' then value ->> 'string_value' end)                  source,
max(case when key = 'engaged_session_event' then value ->> 'int_value' end::int) engaged_session_event
from data
group by event_date, event_timestamp, event_name;

SELECT event_date, event_timestamp, event_name
,jsonb_path_query_first(event_params, '$[*] ? (@.key == "session_engaged")') #>> '{value,string_value}' as session_engaged
,jsonb_path_query_first(event_params, '$[*] ? (@.key == "source")') #>> '{value,string_value}' as source
,(jsonb_path_query_first(event_params, '$[*] ? (@.key == "engaged_session_event")') #>> '{value,int_value}')::int as engaged_session_event
from sampleData;