I'm trying to construct a JSON object and I want to query out specific results, specifically, I'm going to use Postgres reading JSON data.

Example 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}}}
  ]
}

Some of the queries I want to run:

1. Select all the port names (p1,p2,p3)
2. Select the number of ports (3)
3. Select all the interactions_dynamics in for for port p2 ("rds_min":6,"rds_max":8)
4. Select the ports with interactions_type -> num_rds >= 7 (p2,p3)
5. Select the port name, interactions_type -> num_wrts, interactions_dynamics -> rds_min where ineraction_dynamics -> rds_max > 20 ("p3,6,5")

You get the idea, SQL-like flexibility. The JSON structure I have is probably wrong to support what I need to do, either that or I don't know how to write the queries.

Can anyone suggest a better way to structure this?

CodePudding user response：

According to which version you use in the database, you can use Postgres document json for extract data from JSON.

Demo

-- 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;