PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to write a query to fetch table columns and json column array elements as columns in the same row in Postgres

I have following data in my table:
enter image description here

I need to develop a SELECT statement which returns the output as follows:

enter image description here

Following is the script to create table and generate above data:

CREATE TABLE employees.employee_data (
    emp_id              varchar(50)  PRIMARY KEY,
    associations        JSONB NOT NULL default '{}'::jsonb
);

INSERT INTO employees.employee_data
(emp_id, associations)
VALUES('1001', '{
                              "group_tags": [
                                {
                                  "orgId": "XYZCompany",
                                  "locationId": "Location01"
                                },
                                {
                                  "orgId": "XYZCompany",
                                  "locationId": "Location02"
                                }                               
                              ]
                            }'::jsonb);
                            

INSERT INTO employees.employee_data
(emp_id, associations)
VALUES('1002', '{
                              "group_tags": [
                                {
                                  "orgId": "ABCCompany",
                                  "locationId": "Location03"
                                },
                                {
                                  "orgId": "ABCCompany",
                                  "locationId": "Location04"
                                }                               
                              ]
                            }'::jsonb);

Can anyone help me out on this?

Tags:

2

Answers


  1. 0

    Just do a query like this

    select emp_id , jsonb_array_elements(jsonb_path_query_array(associations , '$.group_tags[*].orgId')) orgId, 
jsonb_array_elements(jsonb_path_query_array(associations , '$.group_tags[*].locationId')) locationId
from employees.employee_data;

    A version returning text instead of JSON values

    select emp_id, 
jsonb_array_elements_text(jsonb_path_query_array(associations , '$.group_tags[*].orgId')) orgId, 
jsonb_array_elements_text(jsonb_path_query_array(associations , '$.group_tags[*].locationId')) locationId
from employees.employee_data;

    Fiddle to test

    Login or Signup to reply.
  2. 0

    Just use the function jsonb_array_elements() to get all array elements and extract the content you need:

    SELECT emp_id
    ,   content->>'orgId'
    ,   content->>'locationId'
FROM employees.employee_data
    , jsonb_array_elements(associations->'group_tags') jae(content);

    The operator ->> returns text from a json element, while -> returns json.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search