How to transform a string from a json?

Question

I'm working on a postgresql base that was powered by a MongoDB. The intake was all in JSON, it happens that some fields came as STRING and I am unable to retrieve the value.

For example, The field and value are as follows:

"Info":
"{\" categoryName \ ": \" Sale \ ", \" seller \ ": \" Uber \ ", \" categoryId \ ": \" 022 \ ", \" Payment \ ": false}"

Already tried using REPLACE, SPLIT_PART, CONVERTER STRING PRA JSON but nothing worked.

SELECT case 
    when json->'payload'->'com'->0->>'Info' is null 
    then '' 
    else json->'payload'->'com'->0->>'Info'::json 
end Test
FROM tableJson

I need to turn this line into JSON:

"Info": {
"categoryName": "Sale",
"seller": "Uber",
"categoryId": "022",
"Payment": false "}

What exactly are you asking? you are trying to import json files into postgresql? are you using some programming language or are you trying to import directly from json into the database? Try this link, you may need to reimport if it imported wrong, https://stackoverflow.com/questions/39224382/how-can-i-import-a-json-file-into-postgresql — Theresa Forster, Oct 02 '19 at 20:15
The records were imported from MongoDB pro PostgreSQL via ETL. I can extract several records from there, it turns out that some fields have all the value in STRING. — Eduardo Santana, Oct 02 '19 at 20:30
What fields, its not clear what the exact problem is? If it is just the numerals then you just need to literally just remove the " from the numbers, so to change your example to correct json just remove the " from 022, but then you would have problems where it would import in as 22 not 022. eg { "employee":{ "name":"John", "age":30, "city":"New York" } } — Theresa Forster, Oct 02 '19 at 20:35

score 0 · Answer 1 · answered Oct 02 '19 at 20:43

You need to do some regex-replacing to remove all the backslashes, and then format the outer node with curly brackets {}:

postgres=# select cast('{"Info":
{"categoryName": "Sale", "seller": "Uber", "categoryId": "022", "Payment": false}}' as json);
                                        json                                        
------------------------------------------------------------------------------------
 {"Info":                                                                          +
 {"categoryName": "Sale", "seller": "Uber", "categoryId": "022", "Payment": false}}
(1 row)

Guys, I managed to solve the problem in a simple way. I made a CAST in the query and solved. Following the consultation: select ((json_c -> 'payload' -> 'comm' -> 0 - >> 'Info') :: json) -> 'sel' test FROM jsonTable — Eduardo Santana, Oct 03 '19 at 16:43

How to transform a string from a json?

1 Answers1