trviph
/ get_all_json_keys.sql
Created
May 14, 2026 09:10
This SQL query recursively scans every record of a JSON column to produce a unique list of every nested field path present across the entire dataset.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| WITH RECURSIVE json_paths AS ( | |
| -- Start with top-level keys | |
| SELECT | |
| jt.key_name as full_path, | |
| JSON_EXTRACT(CHANGE_ME_COLUMN_NAME, CONCAT('$.', jt.key_name)) as val | |
| FROM CHANGE_ME_TABLE_NAME, | |
| JSON_TABLE(JSON_KEYS(CHANGE_ME_COLUMN_NAME), '$[*]' COLUMNS (key_name VARCHAR(255) PATH '$')) jt | |
| UNION ALL |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| import re | |
| # https://en.wikipedia.org/wiki/Unicode_block | |
| EMOJI_PATTERN = re.compile( | |
| "[" | |
| "\U0001F1E0-\U0001F1FF" # flags (iOS) | |
| "\U0001F300-\U0001F5FF" # symbols & pictographs | |
| "\U0001F600-\U0001F64F" # emoticons | |
| "\U0001F680-\U0001F6FF" # transport & map symbols | |
| "\U0001F700-\U0001F77F" # alchemical symbols |