JSON是否支持SQL可以查询的对象列表

Question

我正在尝试构建一个JSON对象，我想查询出特定的结果，特别是，我将使用Postgres读取JSON数据。

示例JSON:

{
"ports": [
{"p1":{"interactions_type":{"num_rds":4,"num_wrts":8},"interactions_dynamics":{"rds_min":1,"rds_max":10}}},
{"p2":{"interactions_type":{"num_rds":7,"num_wrts":2},"interactions_dynamics":{"rds_min":6,"rds_max":8}}},
{"p3":{"interactions_type":{"num_rds":14,"num_wrts":6},"interactions_dynamics":{"rds_min":5,"rds_max":50}}}
]
}

我想运行的一些查询：

1. 选择所有端口名称(p1、p2、p3(
2. 选择端口数(3(
3. 为端口p2选择所有的interactions_dynamics("rds_min"：6，"rds_max"：8(
4. 选择带有interactions_type->num_rds>=7(p2，p3(
5. 选择端口名称interactions_type->num_wrts、interactions_dynamics->rds_min其中ineraction_dynamics->rds_max>20("p3,6,5"(

您已经了解了类似SQL的灵活性。我的JSON结构可能是错误的，无法支持我需要做的事情，或者我不知道如何编写查询。

有人能提出一个更好的结构方法吗？

Answer 1

根据您在数据库中使用的版本，您可以使用Postgres文档json从json中提取数据。

演示

-- select all the port names (p1,p2,p3)
---------------------------------------
select
jsonb_object_keys(jp) as "port names"
from 
test t
cross join jsonb_array_elements(t.data -> 'ports') jp;

-- select the number of ports (3)
---------------------------------

select
jsonb_array_length(t.data -> 'ports') as "number of ports"
from 
test t;

-- select all the interactions_dynamics infor for port p2 ("rds_min":6,"rds_max":8)
-----------------------------------------------------------------------------------
select
jp -> 'p2' -> 'interactions_dynamics'
from 
test t
cross join jsonb_array_elements(t.data -> 'ports') jp
where
jp ? 'p2';

-- select the ports with interactions_type -> num_rds >= 7 (p2,p3)
------------------------------------------------------------------
select
jpv.key
from 
test t
cross join jsonb_array_elements(t.data -> 'ports') jp
cross join jsonb_each(jp) jpv
where
(jpv.value -> 'interactions_type' ->> 'num_rds') :: int >= 7;

-- select the port name, interactions_type -> num_wrts, interactions_dynamics -> rds_min where ineraction_dynamics -> rds_max > 20 ("p3,6,5")
---------------------------------------------------------------------------------------------------------------------------------------------
select
jpv.key,
(jpv.value -> 'interactions_type' ->> 'num_wrts') :: int,
(jpv.value -> 'interactions_dynamics' ->> 'rds_min') :: int
from 
test t
cross join jsonb_array_elements(t.data -> 'ports') jp
cross join jsonb_each(jp) jpv
where
(jpv.value -> 'interactions_dynamics' ->> 'rds_max') :: int >= 20;