142. Querying JSON columns

Let’s think about what tools do we need to query JSON columns. We know JSON objects are key/value pairs and sometimes value holds a single value (like number or text) or yet another JSON object, which is a key/value pair and… RECURSION

->>

For single values we need a way to extract them. The value of “test_variation” key is a string, we need a way to extract it. It’s done via ->> operator:

SELECT custom_parameters ->> 'test_variation' AS variation, * FROM web_analytics.pageviews WHERE custom_parameters::text != '{}' 

Note how...