Manipulating the JSON column and split the values

Question

I have the following text in one of my Postgres table as TEXT datatype:

[
 {"type": "text", "values": ["General"], "valueType": "string", "fieldType": "text", "value": ["General"], "customFieldId": "ee", "name": "customer_group"}, 
 {"type": "text", "values": ["Vienna"], "valueType": "string", "fieldType": "text", "value": ["Vienna"], "customFieldId": "eU", "name": "customer_city"},
 {"type": "text", "values": ["Mario"], "valueType": "string", "fieldType": "text", "value": ["Mario"], "customFieldId": "eZ", "name": "first_name"},
 {"type": "text", "values": ["2019-06-30"], "valueType": "date", "fieldType": "text", "value": ["2019-06-30"], "customFieldId": "ea", "name": "created_at_date"}
]

I need to split the values of this TEXT field to columns and rows. For that I have converted the TEXT column to JSON as below:

SELECT CAST( "customFieldValues" as JSON) "customFieldValues" FROM fr.contacts

But when I tried to manipulate this JSON value I'm getting NULL as result.

WITH  CTE AS(SELECT CAST( "customFieldValues" as JSON) "customFieldValues" FROM fr.contacts
)
 SELECT
   "customFieldValues" ->>'customer_city' as dd
 FROM CTE

Does anyone have any suggestions on this? How to get the column names and it's values in rows. I want to create a TABLE based on this data.

Any suggestions would be of great help.

below is the expected result,

customer_group   customer_city     first_name       created_at_date
General          Vienna            Mario            2019-06-30

There is no such element as 'sending_status_update'. Of course this gives NULL. Please show us the expected result. — S-Man
– S-Man, Commented Jul 10, 2019 at 13:22
@S-Man Thanks a lot for the swift response! I have edited the code to the correct element(I have copied only a part of the code here and the column sending_status_update is there in the original code), But even if i give the existing code it is showing as NULL. Will post the sample result set. — Linu
– Linu, Commented Jul 10, 2019 at 13:31
... you change the query code into customer_city. But in you example this element does not exist as well... Please work more precisely. — S-Man
– S-Man, Commented Jul 10, 2019 at 13:49
@S-Man The second line of the sample JSON string which i mentioned above contains the customer_city , This is the column name and the value is "".........{"type": "text", "values": ["Vienna"], "valueType": "string", "fieldType": "text", "value": ["Spaichingen"], "customFieldId": "eU", "name": "customer_city"}, — Linu
– Linu, Commented Jul 10, 2019 at 13:54
@S-Man "name": "customer_city", That is existing right? or Can you correct me if i'm wrong? — Linu
– Linu, Commented Jul 10, 2019 at 13:55

S-Man · Accepted Answer · 2019-07-10 14:33:01Z

Disclaimer: It is still not clear:

Why is there one element values and one value? What is the difference?
Why are these elements arrays?

step-by-step demo:db<>fiddle

SELECT 
    MAX(value) FILTER (WHERE column_name = 'customer_group') AS customer_group,
    MAX(value) FILTER (WHERE column_name = 'customer_city') AS customer_city,
    MAX(value) FILTER (WHERE column_name = 'first_name') AS first_name,
    MAX(value) FILTER (WHERE column_name = 'created_at_date') AS created_at_date
FROM (
    SELECT
        elems ->> 'name' AS column_name,
        elems -> 'value' ->> 0 AS value,
        data
    FROM
        mytable,
        json_array_elements(data::json) elems
) s
GROUP BY data

Cast text to json with ::json
Expand the JSON array: One row for each element with json_array_elements()
Getting the value: -> 'value' gets the array, ->> 0 gets the text representation of the first array element (the only one here)
Getting the column: ->> 'name' gets the text representation of the column name
Classical pivot algorithm (turning rows to columns) with the FILTER clause.

Collectives™ on Stack Overflow

Manipulating the JSON column and split the values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related