Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- {
- timestamp: 1558475434,
- request_id: "83e21b28-7c12-11e9-8f9e-2a86e4085a59",
- user_id: "example_user_id_1",
- traits: {
- this: "is",
- dynamic: "json",
- as: ["defined","by","the", "client"]
- }
- }
- SELECT DISTINCT trait, min_by(json_extract(traits, concat('$.', cast(trait AS varchar))), received_at) AS value
- FROM TABLE
- CROSS JOIN UNNEST(regexp_extract_all(traits,'"([^"]+)"s*:s*("[^"]+"|[^,{}]+)', 1)) AS t(trait)
- WHERE json_extract(traits, concat('$.', cast(trait AS varchar))) IS NOT NULL OR json_size(traits, concat('$.', cast(trait AS varchar))) <> 0
- GROUP BY trait
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement