字符串字节到字符串 - 大查询 [英] String byte to String - Big query
问题描述
我有下面的查询,它生成的数据添加了 \
>不确定是否字符串字节导致此问题,因为 string_agg
产生字符串字节作为输出。
#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产品
FROM
table_a
LEFT JOIN
UNNEST(hits)AS hits
LIMIT 10
以下是它正在生产的产品。在产品列中, productCustDimension
价值是我正在采取的,添加了\
字符。
p>
我找到了一种方法来摆脱我提到的使用替换函数的额外字符,但它变得太脏了。我正在寻找是否有更好的方法来做到这一点。我想要产品栏如下
问题在于在 TO_JSON_STRING 。尝试像这样运行查询:
...
ARRAY(SELECT STRING_AGG(CONCAT('{',CAST(index AS STRING),':','',IFNULL(value,''),'','}'),',')FROM UNNEST(customDimensions))作为productCustDimension
...
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
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
...
这篇关于字符串字节到字符串 - 大查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!