KEITH LARD

[supple raft]

I've figured out my group by issue, I'm just wondering how I can query metadata now

[proven torrent]

Yes you can

here is an example:
https://stripe.com/docs/stripe-data/query-disputes-and-fraud-data#radar-for-fraud-teams-data

[twilit osprey]

Hi! I'm taking over from my colleague. Please, let me know if you have any other questions.

[supple raft]

Thank you, SQL is one of my weaker points so don't shout at me if I ask how to do a certain query please 🤣

I'm just trying it out now

I am struggling a little bit on how to query metadata

for example I have a charge with metadata key (bookingPickupLocation)

and I want to query by that value, I've had a look through the docs and can't seem to find an example

FROM charges
WHERE status = 'succeeded' AND 
created between date_trunc('month', current_date) and last_day_of_month(current_date) AND
metadata.bookingPickupLocation = 'Value here'
order by created
LIMIT 100```

This is how I'm doing it

metadata.bookingPickupLocation = 'Value here'

[twilit osprey]

Could you please share the Charge ID ch_xxx that you're looking for? Maybe the metadata lives on another object

[supple raft]

ch_3N2phNJsWvZ3mKzL0toY5BfV

I think you may be right, it could be on the payment intent

[twilit osprey]

Try payment_intent.metadata.bookingPickupLocation

[twilit osprey]

Yes, I just checked.

[supple raft]

FAILED: Presto error: TYPE_MISMATCH: line 5:1: Expression payment_intent is not of type ROW

I think I need to join the payment intents table?

[twilit osprey]

Yes, sorry. I don't know much about sigma, but this sounds like the way forward.

[supple raft]

I'll have a play around thank you

Is there any sql experts you could send my way to help me? 🤣 I'm really shit at it 😭

[twilit osprey]

Personally, when I need to write SQL, I talk to ChatGPT. It's great for this type of thing, when you don't know a language very well, but you also only need to use it occasionally.

[supple raft]

I didn't even think of that, thank you I'll give it a go

[twilit osprey]

FROM charges JOIN payment_intents p ON p.id = payment_intent

You can try something like this.

[supple raft]

That looks to be working, it's just still failing when I try to query metadata

I'm not sure I'm doing it correctly

from charges c join payment_intents p on p.id = payment_intent
where c.paid = true and 
c.created between date_trunc('month', current_date) and last_day_of_month(current_date) and
p.metadata.bookingPickupLocation = 'VALUE HERE'
order by c.created
LIMIT 100```

[twilit osprey]

You should do payment_intent.metadata...

[supple raft]

I get the previous error, I think p is fine because it's aliased.

I get FAILED: Presto error: COLUMN_NOT_FOUND: line 5:1: Column 'p.metadata.bookingpickuplocation' cannot be resolved when I use p.metadta...

[twilit osprey]

What happens if you use payment_intent.metadata.bookingPickupLocation?

[supple raft]

FAILED: Presto error: TYPE_MISMATCH: line 5:1: Expression payment_intent is not of type ROW

[twilit osprey]

Hm, I see. I'm sorry, I think it's better if you talk to Stripe Support about this: https://support.stripe.com/?contact=true

I'd love to help, but we don't work much with Sigma here.

Please, let me know if you have any other questions.