英文:
SELECT AS STRUCT to json
问题
Is there a way to turn a STRUCT into JSON in the SELECT AS STRUCT construction in JSON? For example:
WITH tbl AS (
SELECT "val.num" AS field_path, "INT64" AS data_type UNION ALL
SELECT "val.str", "STRING"
) SELECT AS STRUCT field_path, data_type FROM tbl
How could I get this into the JSON value {"val.num": "INT64", "val.str": "STRING"}?
英文:
Is there a way to turn a STRUCT into json in the SELECT AS STRUCT construction in json? For example:
WITH tbl AS (
SELECT "val.num" AS field_path, "INT64" AS data_type UNION ALL
SELECT "val.str", "STRING"
) SELECT AS STRUCT field_path, data_type FROM tbl
How could I get this into the JSON value {"val.num": "INT64", "val.str": "STRING"} ?
答案1
得分: 0
Sure, here's the translated code portion:
> 如何将此内容转换为JSON值{"val.num": "INT64", "val.str": "STRING"}？
不确定是否有其他方法可以实现，但以下可能是一种可以用来生成您期望的输出的选项。
我已经在一个UDF中实现了它，该UDF将`键值对`作为参数，并返回`JSON`对象。
```sql
CREATE TEMP FUNCTION kv2json (pairs ARRAY<STRUCT<k STRING, v STRING>>) AS ((
SELECT PARSE_JSON(CONCAT('{"', STRING_AGG(k || '":"'|| v, '","'), '"}'))
FROM UNNEST(pairs)
));
WITH tbl AS (
SELECT "val.num" AS field_path, "INT64" AS data_type UNION ALL
SELECT "val.str", "STRING"
)
SELECT kv2json(ARRAY_AGG(STRUCT(field_path, data_type)))
FROM tbl;
Note: This translation includes the code portion you provided, but without the code block formatting, which is not supported in this text-based format.
<details>
<summary>英文:</summary>
> How could I get this into the JSON value {"val.num": "INT64", "val.str": "STRING"} ?
Not sure there is other way to do it without string manipulation. Anyway below might be one option you can use to generate your expected output.
I've implemented it in an UDF which takes `key-value pairs` as a parameter and returns `JSON` object.
```sql
CREATE TEMP FUNCTION kv2json (pairs ARRAY<STRUCT<k STRING, v STRING>>) AS ((
SELECT PARSE_JSON(CONCAT('{"', STRING_AGG(k || '":"' || v, '","'), '"}'))
FROM UNNEST(pairs)
));
WITH tbl AS (
SELECT "val.num" AS field_path, "INT64" AS data_type UNION ALL
SELECT "val.str", "STRING"
)
SELECT kv2json(ARRAY_AGG(STRUCT(field_path, data_type)))
FROM tbl;
答案2
得分: -1
以下是翻译好的部分：
也许最简单的方法是创建一个包含这些配对的数组。例如：
WITH tbl AS (
SELECT "val.num" AS field_path, "INT64" AS data_type UNION ALL
SELECT "val.str", "STRING"
)
SELECT ARRAY(SELECT AS STRUCT field_path, data_type FROM tbl)
如果您想将其转换为 json，可以使用 TO_JSON(ARRAY(...))。
然而，这不是键/值形式，而是一组键-值对的数组。我不太确定如何将其转换为单个键/值对象。
英文:
Perhaps the simplest way would be to create an array of these pairs. For example:
WITH tbl AS (
SELECT "val.num" AS field_path, "INT64" AS data_type UNION ALL
SELECT "val.str", "STRING"
)
SELECT ARRAY(SELECT AS STRUCT field_path, data_type FROM tbl)
And if you want to turn that into json, do it as TO_JSON(ARRAY(...)).
However, this is not in k/v form but an array of key-values. I'm not quite sure how to get that into a single k/v object.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论