sql - 从 SQL 表中的 JSON 列中检索数据
问题描述
我想知道如何从 SQL 中的 json 列中检索数据。regexp_substr 可能会起作用,也可能会像 Vertica 表中的那样起作用。我是新手,非常感谢任何可以帮助我理解如何的帮助,例如:
- 查找特定 OFFICE_ID 的 prem_amount
我应该把下面的整个结构分成更小的部分来理解结构吗?
[{"Constraint_a":{"match_all":false,"children":[{"match_all":true,"constraints":[{"operator":"GREATER_OR_EQUAL","property":"RATE","value":"0.850"},{"operator":"GREATER_OR_EQUAL","property":"CALLS_PER_HOUR","value":"1"},{"operator":"GREATER_OR_EQUAL","property":"IN_RATE","value":"1.000"}]}],"constraints":[]},"constraint_ids":["1"],"intervals":[{"end_offset":464400,"start_at":null,"start_offset":457200,"end_at":null}],"id":"1","OFFICE_ID":"1","content_hash_id":"1","inc_params":{"is_commissionable":false,"prem_amount":25.0,"fee":0.1},"filters":[],"visible_office_name":"site_trix","legacy_params":{}},{"Constraint_a":{"match_all":false,"children":[{"match_all":true,"constraints":[{"operator":"GREATER_OR_EQUAL","property":"RATE","value":"0.850"},{"operator":"GREATER_OR_EQUAL","property":"CALLS_PER_HOUR","value":"1"},{"operator":"GREATER_OR_EQUAL","property":"IN_RATE","value":"1.000"}]}],"constraints":[]},"constraint_ids":["2"],"intervals":[{"end_offset":550800,"start_at":null,"start_offset":543600,"end_at":null}],"id":"2","OFFICE_ID":"2","content_hash_id":"2","inc_params":{"is_commissionable":false,"prem_amount":25.0,"fee":0.1},"filters":[],"visible_office_name":"site_trix","legacy_params":{}},{"Constraint_a":{"match_all":false,"children":[{"match_all":true,"constraints":[{"operator":"GREATER_OR_EQUAL","property":"RATE","value":"0.850"},{"operator":"GREATER_OR_EQUAL","property":"CALLS_PER_HOUR","value":"1.5"},{"operator":"GREATER_OR_EQUAL","property":"IN_RATE","value":"1.000"}]}],"constraints":[]},"constraint_ids":["3"],"intervals":[{"end_offset":471600,"start_at":null,"start_offset":464400,"end_at":null}],"id":"3","OFFICE_ID":"3","content_hash_id":"3","inc_params":{"is_commissionable":false,"prem_amount":25.0,"fee":0.1},"filters":[],"visible_office_name":"site_trix","legacy_params":{}},{"Constraint_a":{"match_all":false,"children":[{"match_all":true,"constraints":[{"operator":"GREATER_OR_EQUAL","property":"RATE","value":"0.850"},{"operator":"GREATER_OR_EQUAL","property":"CALLS_PER_HOUR","value":"1.5"},{"operator":"GREATER_OR_EQUAL","property":"IN_RATE","value":"1.000"}]}],"constraints":[]},"constraint_ids":["4"],"intervals":[{"end_offset":558000,"start_at":null,"start_offset":550800,"end_at":null}],"id":"4","OFFICE_ID":"4","content_hash_id":"4","inc_params":{"is_commissionable":false,"prem_amount":25.0,"fee":0.1},"filters":[],"visible_office_name":"site_trix","legacy_params":{}},{"Constraint_a":{"match_all":false,"children":[{"match_all":true,"constraints":[{"operator":"GREATER_OR_EQUAL","property":"RATE","value":"0.850"},{"operator":"GREATER_OR_EQUAL","property":"CALLS_PER_HOUR","value":"1"},{"operator":"GREATER_OR_EQUAL","property":"IN_RATE","value":"1.000"}]}],"constraints":[]},"constraint_ids":["5"],"intervals":[{"end_offset":478800,"start_at":null,"start_offset":471600,"end_at":null}],"id":"5","OFFICE_ID":"5","content_hash_id":"5","inc_params":{"is_commissionable":false,"prem_amount":20.0,"fee":0.1},"filters":[],"visible_office_name":"site_trix","legacy_params":{}},{"Constraint_a":{"match_all":false,"children":[{"match_all":true,"constraints":[{"operator":"GREATER_OR_EQUAL","property":"RATE","value":"0.850"},{"operator":"GREATER_OR_EQUAL","property":"CALLS_PER_HOUR","value":"1"},{"operator":"GREATER_OR_EQUAL","property":"IN_RATE","value":"1.000"}]}],"constraints":[]},"constraint_ids":["6"],"intervals":[{"end_offset":565200,"start_at":null,"start_offset":558000,"end_at":null}],"id":"6","OFFICE_ID":"6","content_hash_id":"6","inc_params":{"is_commissionable":false,"prem_amount":20.0,"fee":0.1},"filters":[],"visible_office_name":"site_trix","legacy_params":{}},{"Constraint_a":{"match_all":false,"children":[{"match_all":true,"constraints":[{"operator":"GREATER_OR_EQUAL","property":"RATE","value":"0.850"},{"operator":"GREATER_OR_EQUAL","property":"CALLS_PER_HOUR","value":"1.5"},{"operator":"GREATER_OR_EQUAL","property":"IN_RATE","value":"1.000"}]}],"constraints":[]},"constraint_ids":["7"],"intervals":[{"end_offset":486000,"start_at":null,"start_offset":478800,"end_at":null}],"id":"7","OFFICE_ID":"7","content_hash_id":"7","inc_params":{"is_commissionable":false,"prem_amount":25.0,"fee":0.1},"filters":[],"visible_office_name":"site_trix","legacy_params":{}},{"Constraint_a":{"match_all":false,"children":[{"match_all":true,"constraints":[{"operator":"GREATER_OR_EQUAL","property":"RATE","value":"0.850"},{"operator":"GREATER_OR_EQUAL","property":"CALLS_PER_HOUR","value":"1.5"},{"operator":"GREATER_OR_EQUAL","property":"IN_RATE","value":"1.000"}]}],"constraints":[]},"constraint_ids":["8"],"intervals":[{"end_offset":572400,"start_at":null,"start_offset":565200,"end_at":null}],"id":"8","OFFICE_ID":"8","content_hash_id":"8","inc_params":{"is_commissionable":false,"prem_amount":25.0,"fee":0.1},"filters":[],"visible_office_name":"site_trix","legacy_params":{}},{"Constraint_a":{"match_all":false,"children":[{"match_all":true,"constraints":[{"operator":"GREATER_OR_EQUAL","property":"RATE","value":"0.850"},{"operator":"GREATER_OR_EQUAL","property":"CALLS_PER_HOUR","value":"1.5"},{"operator":"GREATER_OR_EQUAL","property":"IN_RATE","value":"1.000"}]}],"constraints":[]},"constraint_ids":["9"],"intervals":[{"end_offset":493200,"start_at":null,"start_offset":486000,"end_at":null}],"id":"9","OFFICE_ID":"9","content_hash_id":"9","inc_params":{"is_commissionable":false,"prem_amount":25.0,"fee":0.1},"filters":[],"visible_office_name":"site_trix","legacy_params":{}},{"Constraint_a":{"match_all":false,"children":[{"match_all":true,"constraints":[{"operator":"GREATER_OR_EQUAL","property":"RATE","value":"0.850"},{"operator":"GREATER_OR_EQUAL","property":"CALLS_PER_HOUR","value":"1.5"},{"operator":"GREATER_OR_EQUAL","property":"IN_RATE","value":"1.000"}]}],"constraints":[]},"constraint_ids":["10"],"intervals":[{"end_offset":579600,"start_at":null,"start_offset":572400,"end_at":null}],"id":"10","OFFICE_ID":"10","content_hash_id":"10","inc_params":{"is_commissionable":false,"prem_amount":25.0,"fee":0.1},"filters":[],"visible_office_name":"site_trix","legacy_params":{}},{"Constraint_a":{"match_all":false,"children":[{"match_all":true,"constraints":[{"operator":"GREATER_OR_EQUAL","property":"RATE","value":"0.850"},{"operator":"GREATER_OR_EQUAL","property":"CALLS_PER_HOUR","value":"1"},{"operator":"GREATER_OR_EQUAL","property":"IN_RATE","value":"1.000"}]}],"constraints":[]},"constraint_ids":["11"],"intervals":[{"end_offset":500400,"start_at":null,"start_offset":493200,"end_at":null}],"id":"11","OFFICE_ID":"11","content_hash_id":"11","inc_params":{"is_commissionable":false,"prem_amount":20.0,"fee":0.1},"filters":[],"visible_office_name":"site_trix","legacy_params":{}},{"Constraint_a":{"match_all":false,"children":[{"match_all":true,"constraints":[{"operator":"GREATER_OR_EQUAL","property":"RATE","value":"0.850"},{"operator":"GREATER_OR_EQUAL","property":"CALLS_PER_HOUR","value":"1"},{"operator":"GREATER_OR_EQUAL","property":"IN_RATE","value":"1.000"}]}],"constraints":[]},"constraint_ids":["12"],"intervals":[{"end_offset":586800,"start_at":null,"start_offset":579600,"end_at":null}],"id":"12","OFFICE_ID":"12","content_hash_id":"12","inc_params":{"is_commissionable":false,"prem_amount":20.0,"fee":0.1},"filters":[],"visible_office_name":"site_trix","legacy_params":{}}]
谢谢你的帮助!
解决方案
您是如何将 JSON 数据导入 Vertica 的?您是否将其放入 VARCHAR 中?这并不理想,因为您无法轻松验证它(如this answer所示)或从中提取值。相反,您可以使用FJSONParser加载 JSON 数据。例如:
vsql> create table super(age int, name varchar);
vsql> copy super from stdin parser fjsonparser();
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> {"age": 5, "name": "Tim"}
>> {"age": 3}
>> {"name": "Fred"}
>> {"name": "Bob", "age": 10}
>> \.
vsql> select * from super;
age | name
-----+------
| Fred
10 | Bob
5 | Tim
3 |
(4 rows)
显然你不会在 STDIN 上输入你的 JSON;这只是文档中的示例。您可以像这样从 JSON 文件加载:
vsql> COPY SE.PostLinks FROM '"$loaddir"/PostLinks.json' PARSER fjsonparser();
(猜猜我在试验什么数据。:-))
推荐阅读
- python - 使用 print() 函数时 Python 不会显示文本
- javascript - 从非异步函数中获取异步函数的承诺
- casting - 雪花 - 使用可能的空字符串将变体转换为浮动
- android - 如何设置android推送通知颜色和图标?
- apache-spark - 如果列存在于当前 DF 中,则过滤到新 DF
- google-apps-script - 您如何在另一个电子表格中运行在一个电子表格中制作的用户制作的 Google 脚本功能
- javascript - Javascript 页面加载延迟
- python - 如何在 Python 中找到整数或浮点数的绝对值,而不使用库、除法、平方根、条件或内置函数
- snowflake-cloud-data-platform - 复制到更新插入/覆盖
- javascript - 滚动过去后如何获取元素的高度?