mysql json queries

console.sql

select properties->>"$.cow" from ahoy_events where properties->>"$.cow" is not null;
-- Finds rows where cow attr is not missing - BUT not where cow attr is not null
# null
# 1
# 2
# 3
# null

select properties->>"$.cow" from ahoy_events where properties->>"$.cow" != CAST('null' AS JSON);
-- Finds rows where cow attr is not missing - BUT not where cow attr is not null
# null
# 1
# 2
# 3
# null

select properties->>"$.cow" from ahoy_events where JSON_EXTRACT(`properties`, '$.cow') != CAST('null' AS JSON);
-- Finds rows where value of cow is not missing and not null
# 1
# 2
# 3

select JSON_EXTRACT(`properties`, '$.cow') from ahoy_events where JSON_EXTRACT(`properties`, '$.cow') != CAST('null' AS JSON);
-- Finds rows where value of cow is not missing and not null
# 1
# 2
# 3

select properties->>"$.cow" from ahoy_events where properties->>"$.cow" != 'null';
-- Finds rows where value of cow is not missing and not null
# 1
# 2
# 3

select properties->>"$.cow" from ahoy_events where properties is not null;
-- Finds all rows where properties is not null - displays just the cow value
-- Note that the <null> values indicate that cow attr was not found
# <null>
# ...
# <null>
# null
# 1
# 2
# 3
# null