CODEWITHSUNDEEP
sqljsonhierarchical-dataprestoamazon-athena
Ram
Ram

Reputation: 319

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

Expected format is as follows:

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

    ]
}

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

Upvotes: 6

Views: 6007

Answers (1)

ebyhr
ebyhr

Reputation: 1637

You can just cast it to JSON.

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

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

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

Upvotes: 1

Related Questions