I am trying to search for a row that has certain key value pairs in an array. A row in my BigQuery table would look something like this.
{
"ip": "192.168.1.1",
"cookie" [
{
"key": "apple",
"value: "red"
},
{
"key": "orange",
"value: "orange"
},
{
"key": "grape",
"value: "purple"
}
]
}
I thought about using implicit UNNEST or CROSS JOIN like the following, but it didn't work because unnesting it would just create multiple different rows.
SELECT ip
FROM table t, t.cookie c
WHERE (c.key = "grape" AND c.value ="purple") AND (c.key = "orange" AND c.value ="orange")
This link is really close to what I want to do, except they are using legacy SQL
and not standardSQL
To declare a specific data type for an array, use angle brackets ( < and > ). For example: SELECT ARRAY<FLOAT64>[1, 2, 3] as floats; Arrays of most data types, such as INT64 or STRING , don't require that you declare them first.
Results from your query can be sorted by using the order_by argument. The argument can be used to sort nested objects too. The sort order (ascending vs. descending) is set by specifying the asc or desc enum value for the column name in the order_by input object, e.g. {name: desc} .
Conclusion. As you can see, SQL Server does not include arrays. But we can use table variables, temporary tables or the STRING_SPLIT function. However, the STRING_SPLIT function is new and can be used only on SQL Server 2016 or later versions.
#standardSQL
SELECT ip
FROM yourTable
WHERE (
SELECT COUNT(1)
FROM UNNEST(cookie) AS pair
WHERE pair IN (('grape', 'purple'), ('orange', 'orange'))
) >= 2
you can test it with below dummy data
#standardSQL
WITH yourTable AS (
SELECT '192.168.1.1' AS ip, [('apple', 'red'), ('orange', 'orange'), ('grape', 'purple')] AS cookie UNION ALL
SELECT '192.168.1.2', [('abc', 'xyz')]
)
SELECT ip
FROM yourTable
WHERE (
SELECT COUNT(1)
FROM UNNEST(cookie) AS pair
WHERE pair IN (('grape', 'purple'), ('orange', 'orange'))
) >= 2
In case if you need output ip if at least one pair is in array - you need to change >= 2
to >=1
in WHERE
clause
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With