How to replace parameter values with column name in a json string in presto

Question

I have a table named sampleTable with values like this (please note that the columns can be up to key100, stating a small example for reducing the complexity of the example).

select * from sampleTable;

key1	key2
1111	2222

I am trying to generate a query which creates one JSON string which can include these column values to one JSON String per row like the below mentioned format.

 '{"keys":{"key1":"1111", "key2":"2222"}}'

What is the right way to do it through a presto query ? My attempts are as given below:

Attempt 1

select 
    concat('{"keys":{"key1": ', a.key1, ',',
    '{"key2": ' ,a.key2, '}}') 
    AS keyJson 
from sampleTable ;

Attempt 2

select 
    replace(
        replace('{"keys":{"key1": $key1, "key2": $key2}}','$key1',d.key1), 
        '$key2',d.key2 ) 
    AS keyJson 
from sampleTable ; 

Is there any simpler way to do this considering that the keys can be varying from key1 to key100. For context, I am dynamically generating the query from a java program. Appending strings using the StringBuilder approach in both of these cases becomes complex in case of presence of multiple keys.

Answer 1

The idiomatic way is to construct a ROW value and then cast it to JSON:

• First, construct a row from the columns: row(key1, key2, key3). This will produce an anonymous row type (i.e., a row with unnamed fields)
• Second, cast the value to a row with named fields: CAST(... AS ROW(key1 VARCHAR, key2 VARCHAR, key3 VARCHAR)). The field names will become the keys when the row is converted to JSON.
• Finally, cast the resulting value to JSON: CAST(... AS JSON)

Here's a complete example:

WITH data(key1, key2, key3) AS (
    VALUES
        ('1111', '2222', '3333')
)
SELECT
    CAST(
        CAST(row(key1, key2, key3) AS ROW(key1 VARCHAR, key2 VARCHAR, key3 VARCHAR)) AS JSON)
FROM data

which produces:

                    _col0
---------------------------------------------
 {"key1":"1111","key2":"2222","key3":"3333"}
(1 row)

Tested with Trino 359 (formerly known as PrestoSQL)