Extract results from Presto in the form of JSON

Question

I have a presto view from where I need to create nested JSON. I have tried with the below query:

SELECT CAST(CAST(ROW('Sales (PTD)' as Ttl,unsale as Val) AS ROW(v1 VARCHAR, v2 VARCHAR)) AS JSON)
from metrics_v where time_frame='PTD';

I need a JSON result like below:

"SlsPTD": {
    "Ttl": "Sales (PTD)",
    "Val": "103.27290344238281"
  }

But it is currently throwing error: SQL Error [1]: Query failed (#20220725_063102_03638_j2tav): line 1:36: mismatched input 'as'. Expecting: ')', ','

How to get the expected result?

Answer 1

But it is currently throwing error: SQL Error 1: Query failed (#20220725_063102_03638_j2tav): line 1:36: mismatched input 'as'. Expecting: ')', ','

the issue here is incorrect syntax for ROW instantioation, correct query can look like:

SELECT CAST(
        CAST(
            ROW('Sales (PTD)', unsale) AS ROW(Ttl VARCHAR, Val VARCHAR)
        ) AS JSON
    )
from dataset
where time_frame = 'PTD';

But ROW is incorrect data structure here, cause presto does not treat it as key-value pairs but rather as a collection of values (i.e. an array, see the cast to json doc). You can use MAP:

-- sample data
WITH dataset(unsale, time_frame) AS (
    VALUES ('103.27290344238281', 'PTD')
) 

-- query
SELECT CAST(
        map(
            array [ 'SlsPTD' ],
            array [ map(array [ 'Ttl', 'Val' ], array [ 'Sales (PTD)', unsale ]) ]
        ) AS JSON
    )
from dataset
where time_frame = 'PTD';

Output:

_col0
{"SlsPTD":{"Ttl":"Sales (PTD)","Val":"103.27290344238281"}}