troubleshooting
How to handle dynamic column names when defining a SOURCE with JSONB in SQL?
Jim is looking for a way to handle dynamic column names when defining a SOURCE in SQL. Martin suggests using JSONB data type for extensions and provides an example SQL query for extracting 'uuid' from the JSONB column.
Ji
Jim
Asked on Oct 19, 2023
- Define a table with a JSONB column for dynamic data:
create table t(
id varchar,
spec_version varchar,
type varchar,
extensions jsonb
);```
2. Extract 'uuid' from the JSONB column using the '->' operator:
```sql
select extensions -> 'extension_type' -> 'uuid' from t;```
3. If 'uuid' is not present in a row, the query will return null for that row.Oct 19, 2023Edited by