How to remove all \ from nested json in SQL Redshift?

Question

I've got some problems with extracting values from nested json values in column. I've got a column of data with values that looks almost like nested json, but some of jsons got \ between values and I need to clean them.

JSON looks like this:

{"mopub_json":
   "{\"currency\":\"USD\",
   \"country\":\"US\",
   \"publisher_revenue\":0.01824}
"}

I need to get currency and publisher revenue as different columns and try this:

SET json_serialization_enable TO true;
SET json_serialization_parse_nested_strings TO true;
SELECT
JSON_EXTRACT_PATH_TEXT(column_name, 'mopub_json', 'publisher_revenue') as revenue_mopub,
JSON_EXTRACT_PATH_TEXT(column_name, 'mopub_json', 'currency') as currency_mopub
FROM(
SELECT replace(column_name, "\t",  '')
FROM table_name)

I receive the next error:

[Amazon](500310) Invalid operation: column "\t" does not exist in events

When I'm trying this:

SET json_serialization_parse_nested_strings TO true;
SELECT
JSON_EXTRACT_PATH_TEXT(column_name, 'mopub_json', 'publisher_revenue') as revenue_mopub,
JSON_EXTRACT_PATH_TEXT(column_name, 'mopub_json', 'currency') as currency_mopub
FROM(
SELECT replace(column_name, chr(92),  '')
FROM table_name)

I receive

Invalid operation: JSON parsing error

When I'm trying to extract values without replacing , I'm receiving empty columns.

Thank you for your help!

replace(column_name, chr(92), '') is working, the problem in my query is about json_extract_path_text. Jsons in my data aren't valid at all, they contain extra brackets and etc, so it's hard to remove all of combinations. — Vladislava Gonchar
– Vladislava Gonchar, Commented Mar 13, 2022 at 14:12

Bill Weiner · Accepted Answer · 2022-03-11 04:10:13Z

1

So your json isn't valid. JSON doesn't allow multiline text strings but I expect that the issue. Based on your query I think you don't want a single key and string but the whole structure. The reason the that quotes are backslashed is because they are inside a string. The json should look like:

{
  "mopub_json": {
    "currency": "USD",
    "country": "US",
    "publisher_revenue": 0.01824
  }
}

Then the SQL you have should work.

answered Mar 11, 2022 at 4:10

Bill Weiner

11.4k2 gold badges12 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to remove all \ from nested json in SQL Redshift?

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