sql - 使用 Presto 从数据构造 json

Question

如果我在表中有如下数据

WITH dataset AS (
  SELECT ARRAY[
    CAST(ROW('Bob', 38) AS ROW(name VARCHAR, age INTEGER)),
    CAST(ROW('Alice', 35) AS ROW(name VARCHAR, age INTEGER)),
    CAST(ROW('Jane', 27) AS ROW(name VARCHAR, age INTEGER))
  ] AS users,
  CAST(ROW('Google') AS ROW(company VARCHAR)) AS company
)
SELECT * FROM dataset

预期格式如下：

{
    "company": "Google",
    "users": [
        {
            "name": "Bob",
            "age": 38
        },
        {
            "name": "Alice",
            "age": 35
        },
        {
            "name": "Jane",
            "age": 27
        }

    ]
}

如何构造从该数据中提取的 JSON？

标签： sqljsonhierarchical-dataprestoamazon-athena

Answer 1

您可以将其转换为 JSON。

...
SELECT cast(users as json), cast(company as json) FROM dataset

https://trino.io/docs/0.172/functions/json.html

如果它没有返回预期的输出，请分享您的预期结果。