Sql中的 postgreSub查询

Question

我在我的应用程序中使用postgres作为数据库。

我有一个jsonb列，用于存储json数据在它里面。

{
"id": "manohar",
"array": [
{
"status": "active",
"date": "13/12/2022"
},
{
"status": "InActive",
"date": "13/12/2021"
}
]
}

所以这里每次我写子查询选择任何字段基于最大日期在一个数组对象。这里的问题是我在每个json对象中有100个字段，所以每次我都必须为每个字段编写子查询。

和使用子查询后在性能上看起来更少。

这是我使用的查询

Select 
(select t.sub_array->>'status'
from tbl_name d
cross join lateral (
select t.item  as sub_array
from jsonb_array_elements(d.column_name -> 'array') as t(item)
where t.item ->> 'date' <= TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD')
order by (t.item ->> 'date')::date desc
limit 1
) t where t.primaryKey = d.key) as status
--like I have to write 100 subqueries for 100's fields 
from tbl_name t
where t.id='3'

我不想用Views来满足这个要求。

任何更好的方法或建议都会有帮助。

谢谢. .

Answer 1

必须使用lateral view来访问JSON。但是，在数据库中存储和访问JSON并不是SQL最佳实践的一部分。

在你的情况下，我只能建议你改变你的数据模型。存储JSON是一件事，你可以使用blob字符串来存储它，这应该没问题。但在你写它的同时，你也应该在表格/列/行中扩展它的内容。因此，您可以更轻松地访问其内容。

思路是:

• 要么你花时间写它正确(做一个好的数据架构)
• 或者你花时间正确地读取它(进行复杂的SQL查询)

---

这只是你应该做的一个例子。您可能需要找到漂亮的名称来反映每个对象的业务价值。

表"id"

<表类> ID 名称 tbody><<tr>1马诺