使用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？

Answer 1

您可以直接将其转换为JSON。

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

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

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