Postgresql, jsonb query help is needed

Question

I am very, very embarrassed. But I need a help with my query, thanks in advance.

My jsonb (jsonb array) is

 [  

   {  
      "id_contact":2,
      "contact_data":{  
         "NM_EMAIL":[  
            "[email protected]"
         ],
         "NM_PHONE":[  
            "849533574",
            "849533d575"
         ]
      },
      "resp_ls_data":[  
         "14",
         "11"
      ],
       "pr_from_head":true
   },
   {  
      "id_contact":8,
      "contact_data":{  
         "NM_EMAIL":[  
            "[email protected]"
         ],
         "NM_PHONE":[  
            "89234511"
         ]
      },
      "resp_ls_data":[  
         null
      ],
       "pr_from_head":false
   }

]

I figured out the query as

select case when pr_from_head then jsonb_build_object('id_head_cont',id_contact)::text
        when not pr_from_head then id_contact::text
        when id_contact is null then NULL end as est_contact_id,
   contact_data,
   nullif(resp_ls_data, '[null]') resp_ls_array
  from jsonb_to_recordset('
  [  

   {  
      "id_contact":2,
      "contact_data":{  
         "NM_EMAIL":[  
            "[email protected]"
         ],
         "NM_PHONE":[  
            "849533574",
            "849533d575"
         ]
      },
      "resp_ls_data":[  
         "14",
         "11"
      ],
       "pr_from_head":true
   },
   {  
      "id_contact":8,
      "contact_data":{  
         "NM_EMAIL":[  
            "[email protected]"
         ],
         "NM_PHONE":[  
            "89234511"
         ]
      },
      "resp_ls_data":[  
         null
      ],
       "pr_from_head":false
   }

]
') as ls(id_contact integer, contact_data jsonb, resp_ls_data jsonb,pr_from_head boolean)

Got the result as :

"est_contact_id"       ||   "contact_data"    || resp_ls_array"
"{""id_head_cont"": 2}"||   "{""NM_EMAIL"":.. || "[""14"", ""11""]"
"8"                    ||   "{""NM_EMAIL"":.."|| NULL

But the result i want is :

 "est_contact_id"       ||  "contact_data"    || 'resp_ls_array"
  "{""id_head_cont"": 2}"|| "{""NM_EMAIL"":.. || "14"
  "{""id_head_cont"": 2}"|| "{""NM_EMAIL"":.. || "11"
  "8"                    || "{""NM_EMAIL"":.."|| NULL

If resp_ls_array is not null I want to separate is to parts. Any help would be appreciated.

klin · Accepted Answer · 2017-03-31 08:31:26Z

1

You can transform the result using jsonb_array_elements(), like in this pseudocode:

select est_contact_id, contact_data, value as resp_ls
from (

    <your query here>

) s
left join jsonb_array_elements(resp_ls_array) on true;

Test it here.

answered Mar 31, 2017 at 8:31

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql, jsonb query help is needed

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related