-
Notifications
You must be signed in to change notification settings - Fork 0
/
query.sql
29 lines (29 loc) · 953 Bytes
/
query.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
SELECT
Channel_Metadata_ID,
ARRAY_AGG(
CASE
WHEN attribute.attributeName IN ('Antibody Name', 'Antibody name') AND attribute.attributeValue IS NOT NULL
THEN attribute.attributeValue
END
IGNORE NULLS) AS Antibody_Name,
ARRAY_AGG(
CASE
WHEN attribute.attributeName IN ('Channel Name', 'Channel', 'CHANNEL', 'channel', 'channelName') OR attribute.attributeName LIKE 'BEMS%'
AND attribute.attributeValue IS NOT NULL
THEN attribute.attributeValue
END
IGNORE NULLS) AS Channel_Name,
ARRAY_AGG(
CASE
WHEN attribute.attributeName IN ('MARKERNAME', 'Markers', 'marker_name') AND attribute.attributeValue IS NOT NULL
THEN attribute.attributeValue
END
IGNORE NULLS) AS Marker_Name
FROM
`htan-dcc.ISB_CGC_r5.channel_metadata`,
UNNEST(channel_attributes) AS attribute
WHERE
Imaging_Assay_Type != 'H&E'
AND Imaging_Assay_Type != 'MERFISH'
GROUP BY
Channel_Metadata_ID