String byte to String - Big query

Question

I have the below query and it produces data with \" added. Not sure if it is string byte causing this problem because string_agg produces string byte as output.

#standardSQL
SELECT
  visitid,
  fullVisitorId,
  hits.hitNumber,
  TO_JSON_STRING(ARRAY(
    SELECT
      AS STRUCT productSKU,
      ARRAY(SELECT STRING_AGG(CONCAT('{"',CAST(index AS STRING), '":', '"', IFNULL(value,''), '"', '}'), ',')  FROM   UNNEST(customDimensions))  as productCustDimension
    FROM
      UNNEST(hits.product) p)) AS product
FROM
  table_a
LEFT JOIN
  UNNEST(hits) AS hits
  LIMIT 10

Below is the output it is producing. In the product column, the productCustDimension value is what i am taking about, with addition "\ characters added.

I found a way to get rid of extra characters I mentioned using replace function, but it kind of becomes too dirty. I am looking if there is a better way of doing it. I want the product column as below

This problem happens (invariably) because you're encoding the value as a string twice, when you meant to do it once. I'm not familiar enough with SQL to tell you where that's happening. — jpaugh
– jpaugh, Commented Jan 29, 2018 at 19:12
I bet it's between the CONCAT and STRING_AGG bits. Looks like both are outputting a string. — jpaugh
– jpaugh, Commented Jan 29, 2018 at 19:13
If the character " is present in your JSON then it should be escaped (that's what BQ is doing by adding ` otherwise you won't have a valid JSON). You can test with simpler values and get the same result: SELECT to_json_string(['"5"', '"6"'])`. If you want a valid json then you probably shouldn't remove those from your output. — Willian Fuks
– Willian Fuks, Commented Jan 29, 2018 at 20:25
The escape character for big query is \ and I tried SELECT to_json_string(['\"5\"', '\"6\"']), but no luck — Lijju Mathew
– Lijju Mathew, Commented Jan 29, 2018 at 20:40
my comment ended up being rendered wrong hehe. I meant BQ adds the \ character. if you run the query I suggested you'll see same results you are getting in your query already. — Willian Fuks
– Willian Fuks, Commented Jan 29, 2018 at 20:43

Lefteris S · Accepted Answer · 2018-02-11 15:16:57Z

1

The problem is with the " character which is escaped in TO_JSON_STRING. Try running the query like so:

...
ARRAY(SELECT STRING_AGG(CONCAT('{',CAST(index AS STRING), ':', '', IFNULL(value,''), '', '}'), ',')  FROM   UNNEST(customDimensions))  as productCustDimension
...

answered Feb 11, 2018 at 15:16

Lefteris S

1,6721 gold badge10 silver badges15 bronze badges

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

Collectives™ on Stack Overflow

String byte to String - Big query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related