使用Presto从数据构造json [英] Construct json from data using Presto

问题描述

如果我在表中有如下数据

If I have a data in a table as follows

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?

How do I a construct a JSON extracted from this data?

推荐答案

您可以将其转换为JSON.

You can just cast it to JSON.

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

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

如果未返回预期的输出，请分享您的预期结果.

If it doesn't return expected output, please share your expected result.

这篇关于使用Presto从数据构造json的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！