英文:
Validate a dynamic json object in postgress
问题
以下是翻译好的部分：
我在 表格a 的 json_data 列中有以下格式的JSON对象：
{
"body": {
"emp number": "***",
"id": "**",
"details": [
{
"location": "***",
"designation": "***",
"employee type": "***"
}
]
}
}
details 是一个数组，它的大小是动态的，但格式保持不变，即它将始终具有键 location。
这个 location 也存在于另一个表格b中。
最佳的方法来验证是否具有多个位置的特定员工在两个表格中具有相同的位置列表是什么？
以下查询适用于一个位置，
select b.location from table b
left join table a
on a.employee_number = b.employee number
where a.location is not null and b.location = (((a.json_data->>'body')::json-> details)->>0)::json->> location
希望这能帮助你。
英文:
I have json object in the below format in a table a column json_data
{
"body": {
"emp number": "***",
"id": "**",
"details": [
{
"location": "***",
"designation": "***",
"employee type": "***"
}
]
}
details is an array and it is dynamic in size, the format remains the same, i.e it will always have the key location
This location is present in another table b.
What is the best way to validate if a specific employee having multiple locations has the same list of locations in both the tables.
The below query works for one location,
select b.location from table b
left join table a
on a.employee_number = b.employee number
where a.location is not null and b.location = (((a.json_data->>'body')::json-> details)->0)::json->> location
答案1
得分: 2
在JSON属性和另一个表之间强制执行关系完整性的可靠方法不存在。使用查询进行验证无法防止不一致。解决方案是采用不同的设计：不要将详细信息存储在JSON数组中。
在您的情况下，最好的解决方案似乎是不要重复存储数据。如果数据库中只有一个地方存储员工和位置之间的关系，就不会出现不一致情况。
英文:
There is no reliable way to enforce relational integrity between an attribute of a JSON and another table. Validation with a query won't prevent inconsistencies. The solution is a different design: don't store the details in a JSON array.
In your case, it seems like the best solution would be not to store the data twice. If there is only one place in the database where relationships between employees and locations are stored, there can be no inconsistency.
答案2
得分: 1
我将聚合在 a.json_data 和表 b 中找到的位置并将其整理到数组中（适当排序），以便对相同员工编号的数组进行比较，并将其用作标量子查询。
英文:
I would aggregate the locations found in a.json_data and table b in arrays (properly sorted) for the same employee number then compare the arrays and use it all as a scalar subquery.
(
select array_agg(e ->> 'location' order by e ->> 'location')
from json_array_elements(a.json_data -> 'body' -> 'details') e
) =
(
select array_agg(b.location order by b.location)
from b
where b.employee_number = a.json_data -> 'body' ->> 'emp number'
)
Unrelated but this is a sub-optimal data design, hard to work with.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论