I have a query that grabs all of the tags associated with an entity and returns the list of tag objects as a single JSON array in a single column for each entity row. So, for instance, a query for my Store objects looks like so:
SELECT associated_tags.tags AS tags, stores.* FROM "stores"
LEFT JOIN (
SELECT taggings.taggable_id as store_id, JSON_AGG(tags.*) as tags
FROM taggings
INNER JOIN (SELECT * FROM tags ORDER BY name) AS tags ON tags.id=taggings.tag_id
WHERE taggings.taggable_type='Store' AND taggings.context='tags'
GROUP BY taggings.taggable_id
) associated_tags ON associated_tags.store_id=stores.id
ORDER BY (associated_tags.tags->0->>'name') desc NULLS LAST, (stores.id) asc NULLS FIRST
What I would like to do is to perform a json array => json array transformation, mapping each tag to its name field. So, for example, instead of doing SELECT associated_tags.tags I would like to do something like SELECT associated_tags.tags->*->>'name' (but the * is invalid syntax...)
The LEFT JOIN for the associated_tags query is a re-usable, auto-generated query - so I would like to avoid changing anything in there if possible; I'd like to keep any changes to the SELECT part of the main query.
Is there a way to do this?
jsonb_path_query_array() in Postgres 12+ does what you want (if I decipher the question correctly).
It's obviously based on jsonb, not on json. So either use jsonb_agg() instead of json_agg() in your subquery, or unravel the subquery to begin with (which would trim some noise). Or cast to jsonb:
SELECT jsonb_path_query_array(associated_tags.tags::jsonb, '$[*].name') AS tags -- !
, stores.*
FROM stores
LEFT JOIN (
-- your "atomic" subquery here
) associated_tags ON associated_tags.store_id = stores.id
ORDER BY associated_tags.tags->0->>'name' DESC NULLS LAST, stores.id NULLS FIRST;