Snowflake Create View with JSON (VARIANT) field as columns with dynamic keys

user_3143629 0
Viewed 606

I am having a problem creating VIEWS with Snowflake that has VARIANT field which stores JSON data whose keys are dynamic and keys definition is stored in another table. So I want to create a VIEW that has dynamic columns based on the foreign key.

Here are my table looks like:

companies:

| id | name      |
| -- | ----      |
| 1  | Company 1 |
| 2  | Company 2 |

invoices:

| id | invoice_number | custom_fields | company_id |
| -- | -------------- | ------------- | ---------- |
| 1  | INV-01         | {"1": "Joe", "3": true, "5": "2020-12-12"} | 1 |
| 2  | INV-01         | {"2":"Hello", "4": 1000} | 2 |

customization_fields:

| id | label     | data_type | company_id |
| -- | -----     | --------- | ---------- |
| 1  | manager   | text      | 1          |
| 2  | reference | text      | 2          |
| 3  | emailed   | boolean   | 1          |
| 4  | account   | integer   | 2          |
| 5  | due_date  | date      | 1          |

So I want to create a view for getting each companies invoices something like:

CREATE OR REPLACE VIEW companies_invoices AS SELECT * FROM invoices WHERE company_id = 1

which should get a result like below:

| id | invoice_number | company_id | manager | emailed | due_date   | 
| -- | -------------- | ---------- | ------- | ------- | --------   |
| 1  | INV-01         | 1          | Joe     | true    | 2020-12-12 |

So my challenge above here is I cannot make sure the keys when I write the query. If I know that I could write

SELECT 
  id, 
  invoice_number, 
  company_id, 
  custom_fields:"1" AS manager,  
  custom_fields:"3" AS emailed, 
  custom_fields:"5" AS due_date 
FROM invoices 
WHERE company_id = 1

These keys and labels are written in the customization_fields table, so I tried different ways and I am not able to do that.

So could anyone tell me if we can do or not? If we can please give me an example so it would really help.

snowflake-cloud-data-platform sql
3 Answers

You cannot do what you want to do with a view. A view has a fixed set of columns and they have specific types. Retrieving a dynamic set of columns requires some other mechanism.

user_11440350

If you're trying to change the number of columns or the names of the columns based on the rows in the customization_fields table, you can't do it in a view.

If you have a defined schema and just need to grab dynamic JSON properties, you may want to consider looking into Snowflake's GET function. It allows you to get any part of a JSON using a string for the path rather than using a literal path in the SQL statement. For example:

create temp table foo(v variant);
insert into foo select parse_json('{ "name":"John", "age":30, "car":null }');

-- This uses a literal path in the SQL to get to a JSON property
select v:name::string as first_name from foo;

-- This uses the GET function to get the value from a path in a string
select get(v, 'name')::string as first_name from foo;

You can replace the 'name' in the second parameter of the GET function with the value stored in the customization_fields table.

user_127563810

In SF, You will have to use a Stored Proc function to retrieve the dynamic set of columns

user_109495560
Related