时间戳

首页 > 解决方案 > 雪花使用 JSON (VARIANT) 字段创建视图作为具有动态键的列

sql - 雪花使用 JSON (VARIANT) 字段创建视图作为具有动态键的列

问题描述

我在使用具有 VARIANT 字段的 Snowflake 创建 VIEWS 时遇到问题，该字段存储 JSON 数据，其键是动态的，键定义存储在另一个表中。所以我想创建一个具有基于外键的动态列的视图。

这是我的表的样子：

公司：

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

发票：

| 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 |

定制字段：

| 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          |

所以我想创建一个视图来获取每个公司的发票，例如：

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

应该得到如下结果：

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

所以我在上面的挑战是我在编写查询时无法确定密钥。如果我知道我可以写

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

这些键和标签都写在customization_fields表中，所以我尝试了不同的方法，但我无法做到。

那么有人能告诉我我们是否可以吗？如果可以的话，请给我一个例子，这样真的很有帮助。

标签： sqlsnowflake-cloud-data-platform

解决方案

你不能用视图做你想做的事。视图具有一组固定的列，并且它们具有特定的类型。检索一组动态列需要一些其他机制。

推荐阅读