5

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?

Ram
  • 189
  • 1
  • 4
  • 19
  • what format do you get back when you run the query exactly? – Itay Sep 30 '20 at 18:21
  • I get the top level columns such as company, users but no information about the nested keys such as name,age – Ram Oct 01 '20 at 04:24
  • The columns return from prestodb python client are as follows: `[('users', 'array(row(name varchar, age integer))', None, None, None, None, None), ('company', 'row(company varchar)', None, None, None, None, None)]` I am not sure how to process the nested row – Ram Oct 01 '20 at 17:08

1 Answers1

1

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.

ebyhr
  • 1,537
  • 9
  • 15
  • 2
    Yeah, it returns as a JSON but without the column/field names.Let me add what i expect as a result. – Ram Sep 29 '20 at 14:50