sql - 在 postgresql 中展平包含字符串和嵌套数组的 jsonb 对象

Question

我在表中的数据为：

column     |datatype
-----------|--------
id         | integer
name       | text
phone      | text
education  | jsonb

示例数据为：

id    |   name   |   phone   |   data   |
------|----------|-----------|----------|
1     |Test User |78545254   | [[{"label": "Type", "value": "Graduate"}, {"From", "value": "01-04-2015"}, {"label": "To", "value": "21-04-2016"}],[{"label": "Type", "value": "Post Graduate"}, {"From", "value": "01-04-2015"}, {"label": "To", "value": "21-04-2016"}]] |
2     |Test Usr  |78545254   | [[{"label": "Type", "value": "Graduate"}, {"From", "value": "01-04-2015"}, {"label": "To", "value": "21-04-2016"}]] |

我想做一个查询，它可以给我输出：

id |  label             |     value       |
---|--------------------|-----------------|
1  | name               | Test User       |
1  | phone              | 78545254        |
1  | education_Type_1   | Graduation      |
1  | education_From_1   | 01-04-2015      |
1  | education_To_1     | 21-04-2016      |
1  | education_Type_2   | Post Graduation |
1  | education_From_2   | 01-04-2015      |
1  | education_To_2     | 21-04-2016      |
2  | name               | Test Usr        |
2  | phone              | 78545254        |
2  | education_Type_1   | Graduation      |
2  | education_From_1   | 01-04-2015      |
2  | education_To_1     | 21-04-2016      |

谁能帮我建立这样的查询？

标签： sqldatabasepostgresql