英文:
TO_JSON on anonymous struct in bigquery
问题
在对匿名结构体执行 TO_JSON(struct) 操作时，例如 (1,2,3)，它会将所有键视为空字符串，因此会在第一个值后面截断所有数值。例如：
SELECT val, TO_JSON(val) FROM UNNEST([(1,2,3)]) as val
是否有一种方法可以获取一些通用的键值或类似于 BigQuery 在界面中呈现的内容，即使是通过替代方法？我唯一看到的另一种将其转换为 JSON 的方法是手动解析格式字符串（!!）。
英文:
When doing TO_JSON(struct) on an anonymous struct, such as (1,2,3), it treats all keys as empty string, and so it chops off all values after the first. For example:
SELECT val, TO_JSON(val) FROM UNNEST([(1,2,3)]) as val
Is there a way to get some generic key value or something, similar to what BigQuery renders in the UI, even if by an alternative method? The only other way I see to get it into json is to manually parse the format string (!!).
答案1
得分: 1
第一个立即可行的选项是使用 STRUCT 操作符，并手动分配别名。这种解决方案的主要缺点是需要手动硬编码表的别名。
SELECT val, TO_JSON(val)
FROM UNNEST([STRUCT(1 AS a,
2 AS b,
3 AS c)]) as val
结果为：
| val. a | val. b | val. c | f0_ |
|---|---|---|---|
| 1 | 2 | 3 | {"a":1,"b":2,"c":3} |
一种抽象出手动分配别名的解决方案，同时允许检索原始结构的所有值，是使用 TO_JSON_STRING 替代 TO_JSON。
SELECT val, TO_JSON_STRING(val)
FROM UNNEST([(1,2,3)]) as val
结果为：
| val. _field_1 | val. _field_2 | val. _field_3 | f0_ |
|---|---|---|---|
| 1 | 2 | 3 | {"":1,"":2,"":3} |
英文:
The first immediate option for this task would be to use the STRUCT operator, and assign manually the aliases. Main drawback of this solution is that you need to hardcode manually aliases of your table.
SELECT val, TO_JSON(val)
FROM UNNEST([STRUCT(1 AS a,
2 AS b,
3 AS c)]) as val
which gives:
| val. a | val. b | val. c | f0_ |
|---|---|---|---|
| 1 | 2 | 3 | {"a":1,"b":2,"c":3} |
One solution that abstracts from assigning the aliases manually, yet allowing you to retrieve all the values of your original struct, is using TO_JSON_STRING in place of TO_JSON.
SELECT val, TO_JSON_STRING(val)
FROM UNNEST([(1,2,3)]) as val
which gives:
| val. _field_1 | val. _field_2 | val. _field_3 | f0_ |
|---|---|---|---|
| 1 | 2 | 3 | {"":1,"":2,"":3} |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论