RE: Using hiveSQL to find content

[dalz]

SELECT
timestamp,
json_metadata

FROM Txcustoms
WHERE 
CONVERT(DATE,timestamp) BETWEEN '2020-12-01' AND '2021-02-28'
AND [tid] in ('ssc-mainnet-hive')
AND [json_metadata] like ('%"DEC"%')
AND [json_metadata] like ('%"null"%')

I use this query for DEC transfers to null ... you need to parse the json data as well

Comments

[abh12345]

Ah nice, so you could do something like this then...

SELECT top 10 
timestamp,
json_value(json_metadata, '$.contractPayload.symbol') as symbol,
json_value(json_metadata, '$.contractPayload.quantity') as quantity,
json_metadata
FROM Txcustoms
WHERE 
CONVERT(DATE,timestamp) BETWEEN '2021-01-01' AND '2021-02-28'
AND [tid] in ('ssc-mainnet-hive')
AND json_value(json_metadata, '$.contractPayload.symbol') = 'DEC'
AND json_value(json_metadata, '$.contractPayload.to')  = 'null'

[dalz]

That will do it :)
Testing it soon!

[mellofello]

Thanks abh12345, you and @dalz really came through for me today. I like how you parsed some of the json string.
FYI - when we use [tid] = ('ssc-mainnet-hive'), we get transfers on the Hive engine.
When we used [tid] = ('sm_token_transfer'), we get in-game transfers.

[abh12345]

Good to know :)

I've not looked at this table much, mainly out of fear of its size and the json parsing, but I think I'll pay a bit more attention to it in future. Thanks for opening up the conversation, and cheers dalz!

[mellofello]

Thanks so much for your prompt reply. Your query sample was very helpful. I was able to update the where clause to obtain exactly what I needed. I had no clue all this data was available all this time. This is very cool.