BigQuery: How to unescape an extra-escaped/serialized JSON string

user_160863 0
Viewed 166

I'm working with a dataset in which JSON-strings are being pulled from a service and have gone through an extra layer of string extraction/processing, such that the JSON syntax in the string itself has been escaped.

It's easier to show as an example — here's a CSV in which body is a JSON-formatted string:

id,body
1,"{""name"":""hello"",""value"":9}"
2,"{""value"":42}"

Here's what I'm working with:

id,body
1,"""{\""name\"":\""hello\"",\""value\"":9}"""
2,"""{\""value\"":42}"""

If I had to guess, it's as if the source service is pulling in the JSON-formatted string of body, as if it were expecting to be serialized as a string value within another JSON string.

Whatever the case, is there a straightforward way to unescape this string so that I can use JSON_EXTRACT, etc. as normal?

google-bigquery sql json
1 Answers

Seems like the easy solution is to run JSON_EXTRACT_SCALAR on the string, and then the JSON_EXTRACT operation I actually want to do, e.g.

SELECT 
  id
  , JSON_EXTRACT(JSON_EXTRACT_SCALAR(body, '$'), '$.value') AS value
FROM 
  datatable
user_1608630
Related