i have a table which contains JSON value and i have manipulated it using the below query,If i specify each column separately then the query will work fine, rather than i want it to be dynamic ,so that if i have added any columns to the JSON string then it will automatically take that corresponding column.
So how can i execute the below generated dynamic query in PostgreSQL?
have tried using EXECUTE but it's showing syntax error near SELECT.
Any suggestions would be of great help.
-- THE CTE will get all the created columns in the JSON string,So if someone add a new column it will take that new column.
WITH CTE AS
(
SELECT DISTINCT
elems ->> 'name' AS column_name
, 'MAX(value) FILTER (WHERE column_name = ''' || CAST(elems ->> 'name' as TEXT) || ''') as '|| CAST(elems ->> 'name' as TEXT)
as selected_customfields
FROM
dl_get_response.gr_contacts,
json_array_elements( "customFieldValues"::json) elems
)
-- below is the actual query i want to execute,The result of the custom_query CTE need to be executed
,custom_query as
(
SELECT '''SELECT ' || string_agg(DISTINCT selected_customfields::character varying, ',') || ',email '
|| 'FROM (
SELECT
elems ->> ''name'' AS column_name,
elems -> ''value'' ->> 0 AS value,
"customFieldValues",email
FROM
dl_get_response.gr_contacts,
json_array_elements( "customFieldValues"::json) elems
) s
GROUP BY "customFieldValues",s.email''' as q
FROM CTE
)
How to execute the string that is generated from the above CTE?
Below is the sample value in the column "customFieldValues(This is of TEXT datatype, The below example will convert it into a JSON type and will get each name and it's value from it.
[
{"value": ["product_101"], "values": ["product_101"], "type": "text", "valueType": "string", "name": "cat_1", "customFieldId": "ef", "fieldType": "text"}
,{"value": ["AXE"], "values": ["AXE"], "type": "text", "valueType": "string", "name": "cat_2", "customFieldId": "e3", "fieldType": "text"}
,{"value": ["General"], "values": ["General"], "type": "text", "valueType": "string", "name": "customer_group", "customFieldId": "ee", "fieldType": "text"}
]
The sample output of the above will be,
Cat_1(Column 1) cat_2(column 2) customer_group(column 3)
product_101 AXE General
So basically in each set i need the "name" data to be the column name and "value" to be that column value.the challenge is that if i give the query hardcoded it will work perfectly like below.But i need to specify each column name "MAX(value) FILTER (WHERE column_name = 'cat_1') AS cat_1_all"like this ,But the values in the "customFieldValues"column can be changed,So whenever a new column is added i need to change this script.So i thought of taking the column names dynamically as shown in the above code,So whatever columns is added it will automatically take the new columns and it's values and form the table structure.
And the topmost code is the one came to my mind,So i created a the whole query as a string variable and i need to execute that, that's what i was trying to do in the topmost code.
Hope i have made it clear, Will be very helpful if you can guide on this.
SELECT email,
MAX(value) FILTER (WHERE column_name = 'cat_1') AS cat_1_all,
MAX(value) FILTER (WHERE column_name = 'cat_2') AS cat_2_all,
MAX(value) FILTER (WHERE column_name = 'customer_group') AS customer_group
FROM (
SELECT
elems ->> 'name' AS column_name,
elems -> 'value' ->> 0 AS value,
"customFieldValues",email
FROM
dl_get_response.gr_contacts,
json_array_elements( "customFieldValues"::json) elems
) s
GROUP BY "customFieldValues",s.email
