CODEWITHSUNDEEP
sqlgoogle-analyticsgoogle-bigquery
knoll
knoll

Reputation: 295

Subtracting Item from Array in BigQuery

I've got a query that keeps track of items in a shopping cart as a user adds items to it. I've got the adding items part down, but I'm having trouble removing a single item based on a given action. Here's an example:

SELECT
  *,
  -- Remove item from cart where item in cart
  CASE WHEN cart_action = 'remove from cart' THEN (SELECT SPLIT(cart, ',') - SPLIT(cart, ',') WHERE SPLIT(cart, ',') = item)
FROM
(
SELECT 
  'remove from cart' AS cart_action,
  'apple' AS item,
  'apple, orange, banana, apple' AS cart
  );

If my cart starts out as 'apple, orange, banana, apple', then I'd like the output to be 'orange, banana, apple'. It doesn't matter what order the items are in.

I'd like to just be able to break the cart string into an array, find one matching item in the array, drop that item, and then return the updated cart.

Upvotes: 1

Views: 6909

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173046

Below example is for BigQuery Standard SQL

#standardSQL
WITH `action` AS (
  SELECT 
    'remove from cart' AS cart_action,
    'apple' AS item,
    'apple, orange, banana, apple' AS cart
)
SELECT 
  *,
  (SELECT STRING_AGG(cart_item) 
    FROM UNNEST(SPLIT(cart)) cart_item 
    WHERE TRIM(cart_item) != item
  ) new_cart
FROM `action`

with result as

Row cart_action         item    cart                            new_cart     
1   remove from cart    apple   apple, orange, banana, apple    orange, banana, apple

Upvotes: 8

Related Questions