Perform a sub query on an Array of strings that comes back from result in SnowFlake

user_1675557 0
Viewed 53

I have a query

SELECT colors, car, FROM inventory

where colors is data type of ARRAY and car is data type VARCHAR

it returns this

|   colors             |      car    |
|["red",blue,"green"]  |   "mustang" |

I want to join each item from the colors ARRAY on a different table. For example, the price based on color are in a table called in prices

SELECT * FROM prices
|   color             |      price    |
|   "red"             |      300000   |
|   "green"           |      500000   |
|   "blue"            |      100000   |

How can I join on a table with a result that returns an ARRAY

snowflake-cloud-data-platform
1 Answers

Using ARRAY_CONTAINS as join condition:

Returns True if the specified variant is found in the specified array.

SELECT *
FROM Inventory i
JOIN prices p
  ON ARRAY_CONTAINS(p.color::variant, i.colors)
user_50708790
Related