Question 239826 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

How to replace JSON escaping characters in dbt

I have a JSON data like this:

{"payload":"{"custom":{"a":{"hs.dl":"hs:\/\/categories\/Z2lkOi8vc2hvcGlmeS9NZW51SXRlbS81NDM2Nzk0NDczODI=","hs.image":"https:\/\/cms-highstreetapp.imgix.net\/denham\/2023\/08\/0657f839-0045-49b0-ba89-fbde3c74f519\/montage20230818-1-kzy7bm.jpg","hs.body":"Reworked in the colours of the season.","hs.video_720":null,"hs.thumbnail":"https:\/\/cms-highstreetapp.imgix.net\/denham\/2023\/08\/0657f839-0045-49b0-ba89-fbde3c74f519\/montage20230818-1-kzy7bm.jpg","hs.title":"Free Move Chinos","hs.video_480":null,"hs.id":"HS_STUDIO-91428"},"i":"31d443e3-1874-4deb-b475-ac10c850451d"},"aps":{"mutable-content":1,"alert":"Free Move Chinos - Reworked in the colours of the season.","category":"category","sound":"default"}}"}

Currently, I am replacing escaping characters this way:

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(value, 'hs.', ''), '\"', '"'), '"{', "{"), '}"', "}"), '\/', "/"), '\n', ' ') AS value

Just wondering is there any better way to do it in dbt and big query?

EDIT:
I created a function to replace JSON escaping characters, but my whole code gets red color due to characters like " any help would be appreciate

CREATE TEMP FUNCTION customReplace(value STRING)
RETURNS STRING
LANGUAGE js AS """
  try {
    value = value.replace(/hs./g, '')
                 .replace(/\"/g, '"')
                 .replace(/"{/g, '{')
                 .replace(/}"/g, '}')
                 .replace(/\//g, '/')
                 .replace(/\n/g, ' ');
    return value;
  } catch (e) {
    return null;
  }
""";
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    After hours of working with JavaScript, I finally found the right method to replace special characters:

    CREATE TEMP FUNCTION fix_JSON_Formatting(value STRING)
RETURNS STRING
LANGUAGE js AS """
  try {
    value = value.replace(/hs\./g, '')
                 .replace(/\\n/g, ' ')
                 .replace(/\\\//g, '/')
                 .replace(/}\"/g, '}')
                 .replace(/\"{/g, '{')
                 .replace(/\\"/g, '"');
    return value;
  } catch(e) {
    return null;
  }
""";

  2. 0

    How to replace JSON escaping characters

    Consider below approach

    regexp_replace(text, r'(\)(.)', r'2')

    You can test it with below example

    with your_table as (
  select '{"payload":"{"custom":{"a":{"hs.dl":"hs:\/\/categories\/Z2lkOi8vc2hvcGlmeS9NZW51SXRlbS81NDM2Nzk0NDczODI=","hs.image":"https:\/\/cms-highstreetapp.imgix.net\/denham\/2023\/08\/0657f839-0045-49b0-ba89-fbde3c74f519\/montage20230818-1-kzy7bm.jpg","hs.body":"Reworked in the colours of the season.","hs.video_720":null,"hs.thumbnail":"https:\/\/cms-highstreetapp.imgix.net\/denham\/2023\/08\/0657f839-0045-49b0-ba89-fbde3c74f519\/montage20230818-1-kzy7bm.jpg","hs.title":"Free Move Chinos","hs.video_480":null,"hs.id":"HS_STUDIO-91428"},"i":"31d443e3-1874-4deb-b475-ac10c850451d"},"aps":{"mutable-content":1,"alert":"Free Move Chinos - Reworked in the colours of the season.","category":"category","sound":"default"}}"}' as text
)
select text
from your_table union all
select regexp_replace(text, r'(\)(.)', r'2')
from your_table

    with output

    enter image description here

    First row – original unescaped JSON and second row with resulted escaped JSON

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search