Big Query - And/Or Operators

Question

I try to get an answer database that gives me all data for Destination US and different origin countries. In one row however may be written CN,HK,JP - meaning numerous things. So, the Query I write is the following, but the answers contain only origin CN or HK but not "CN,JP,HK".
What is the right code?

SELECT destination_country,origin_country, createDate FROM [DataWarehouse.Draft] 
Where destination_country contains "US"
And originCountries In ("CN", "HK")

Row originCountries destinationCountries createWeek
1 CN US 2014W30
2 CN US 2014W30
3 CN US 2014W30
4 CN US 2014W30
5 HK US 2014W30
6 HK US 2014W30

Answer 1

This is an AND/OR question indeed.

Try:

SELECT destination_country,origin_country, createDate FROM [DataWarehouse.Draft] 
Where destination_country contains "US"
And (originCountries CONTAINS "CN" OR originCountries CONTAINS "HK")

Answer 2

Your origin_country data is currently flattened inside the originCountries field. You need to expand this (flattened) field into a repeated field. I don't know your exact schema, but something along the lines of:

SELECT * FROM
  (SELECT destination_country, SPLIT(originCountries, ",") as origin_country,
   createDate FROM [DataWarehouse.Draft])
WHERE destination_country contains "US" And origin_country IN ("CN", "HK")

should do what you need. I don't know if this version performs well or not, but it should do the filtering you want.

See the SPLIT documentation in the string function reference documentation, and the documentation about nested and repeated fields.