I am hoping someone may be able to help with this query!
I am trying to pull out the data which gives me my individual sites and the “Location” and “Custom” tags that are applied to that site, I am hoping someone is able to see what I am missing. I have some SQL code that pulls out the sites and the tags applied to the devices of that site however this is causing some issues for us with regards to reporting. That code is below:
WITH UniqueTags AS (
SELECT
ds.name AS site_name,
dt.tag_type,
ARRAY_AGG(DISTINCT dt.tag_name) AS tags
FROM
dim_site ds
JOIN dim_site_asset dsa ON ds.site_id = dsa.site_id
JOIN dim_asset da ON dsa.asset_id = da.asset_id
JOIN dim_tag_asset dta ON da.asset_id = dta.asset_id
JOIN dim_tag dt ON dta.tag_id = dt.tag_id
GROUP BY
ds.name, dt.tag_type
),
AggregatedTags AS (
SELECT
site_name,
ARRAY_TO_STRING(ARRAY_AGG(DISTINCT tag) FILTER (WHERE tag_type = ‘LOCATION’), ', ') AS “Location Tags”,
ARRAY_TO_STRING(ARRAY_AGG(DISTINCT tag) FILTER (WHERE tag_type = ‘CUSTOM’), ', ') AS “Custom Tags”
FROM
UniqueTags,
UNNEST(tags) AS tag
GROUP BY
site_name
)
SELECT
site_name AS “Site Name”,
“Location Tags”,
“Custom Tags”
FROM
AggregatedTags
ORDER BY
“Site Name”;
Any help would be really appreciated! And thank you in advance!
I see that you havent had a response so i looked this over:
You’re using “smart quotes” (“ and ”) instead of standard SQL double quotes ("). That’ll cause syntax issue, Your also aggregating all tags into an array and then trying to FILTER based on tag_type after unnesting and in turn you are losing the association between each tag and its original tag_type in that process.
you will want to do something possibly like this..
WITH location_tags AS (
SELECT
ds.name AS site_name,
array_to_string(array_agg(DISTINCT dt.tag_name), ', ') AS location_tags
FROM dim_site ds
JOIN dim_site_asset dsa ON ds.site_id = dsa.site_id
JOIN dim_asset da ON dsa.asset_id = da.asset_id
JOIN dim_tag_asset dta ON da.asset_id = dta.asset_id
JOIN dim_tag dt ON dta.tag_id = dt.tag_id
WHERE dt.tag_type = 'LOCATION'
GROUP BY ds.name
),
custom_tags AS (
SELECT
ds.name AS site_name,
array_to_string(array_agg(DISTINCT dt.tag_name), ', ') AS custom_tags
FROM dim_site ds
JOIN dim_site_asset dsa ON ds.site_id = dsa.site_id
JOIN dim_asset da ON dsa.asset_id = da.asset_id
JOIN dim_tag_asset dta ON da.asset_id = dta.asset_id
JOIN dim_tag dt ON dta.tag_id = dt.tag_id
WHERE dt.tag_type = 'CUSTOM'
GROUP BY ds.name
)
SELECT
COALESCE(lt.site_name, ct.site_name) AS "Site Name",
lt.location_tags AS "Location Tags",
ct.custom_tags AS "Custom Tags"
FROM location_tags lt
FULL OUTER JOIN custom_tags ct ON lt.site_name = ct.site_name
ORDER BY "Site Name";
Whats this does is it should Pull LOCATION and CUSTOM tags separately so they keep their context while joinin both tag types back together per site using a FULL OUTER JOIN to include any sites that have only one tag type. This also Uses standard quotes (") for compatibility