Clever Geek Handbook

How to do the same as array_to_json (array_agg (tags. *)) For N columns

I am currently using PostgreSQL JSON capabilities to create JSON objects from my request, so I can easily use it in my application or pass it to the front-end.

array_to_json(array_agg(tags.*))

does exactly what I need (creates JSON objects with columns as keys from the data and converts them to an array), however I haven't found a way to do the same if I only need one or two columns from tags. I have played with various JSON and array functions, but I have never achieved the same result. thanks for the help

Whole request

SELECT
    tags_components.component_id,
    array_to_json(array_agg(tags.*)) as tags
FROM tags_components 
LEFT JOIN tags ON tags.id = tags_components.tag_id 
AND tags_components.component_name = 'company'
GROUP BY tags_components.component_id
+3


source to share


1 answer


Use a derived table, for example:



SELECT
    tags_components.component_id,
    array_to_json(array_agg(tags.*)) as tags
FROM tags_components 
LEFT JOIN (
    SELECT id, name -- only two columns
    FROM tags 
    ) tags
ON tags.id = tags_components.tag_id 
AND tags_components.component_name = 'company'
GROUP BY tags_components.component_id
+4


source






More articles:


All Articles