Presto Build JSON Array with Different Data Types

Question

My goal is to get a JSON array of varchar name, varchar age, and a LIST of books_read (array(varchar)) for EACH id

books_read has following format: ["book1", "book2"]

Example Given Table:

id	name	age	books_read
1	John	21	["book1", "book2"]

Expected Output:

id	info
1	[{"name":"John", "age":"21", "books_read":["book1", "book2"]}]

When I use the following query I get an error (All ARRAY elements must be the same type: row(varchar, varchar)) because books_read is not of type varchar like name and age.

select id, 
array_agg(CAST(MAP_FROM_ENTRIES(ARRAY[
                            ('name', name),
                            ('age', age),
                            ('books_read', books)
                            ]) AS JSON)) AS info
                from tbl
                group by id

Is there an alternative method that allows multiple types as input to the array?

I've also tried doing MAP_CONCAT(MAP_AGG(name), MAP_AGG(age), MULTIMAP_AGG(books_read)) but it also gives me an issue with the books_read column: Unexpected parameters for the function map_concat

Answer 1

Cast data to json before placing it into the map:

-- sample data
WITH dataset (id, name, age, books_read) AS (
    VALUES (1, 'John', 21, array['book1', 'book2'])
) 

-- query
select id,
    cast(
        map(
            array [ 'name', 'age', 'books_read' ],
            array [ cast(name as json), cast(age as json), cast(books_read as json) ]
        ) as json
    ) info
from dataset

Output:

id	info
1	{"age":21,"books_read":["book1","book2"],"name":"John"}