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

How to Join 2 table with json key in Postgres? – Postgresql

I have 2 table

CREATE TABLE json_data_table(d_id int, d1_json json, d2_json json);
CREATE TABLE user_data(uid int, username varchar);

and data respectively are

INSERT INTO user_data VALUES
(1,'test_user_1'),
(2,'test_user_2'),
(3,'test_user_3');

INSERT INTO json_data_table VALUES 
  (1,'{"stage1":1,"stage2":2 }', '{
    "stage1": {
        "date": "12-01-2023",
        "status": "open",
        "uid": "2"
    },
    "stage2": {
        "date": "22-01-2023",
        "status": "close",
        "uid": "1"
    }
}'),
  (2,'{"stage1":11,"stage2":21 }', '{
    "stage1": {
        "date": "21-2-2023",
        "status": "open",
        "uid": "3"
    },
    "stage2": {
        "date": "2-2-2023",
        "status": "close",
        "uid": "2"
    }
}');

Now i want to join user_data table with json_data_table with uid to get username

Expected table output:

enter image description here

I got the other columns by simply fetching json->>key_name here is select query.

SELECT d1_json->>'stage1' as stage1, 
       d1_json->>'stage2' as stage2, 
       d2_json->'stage1'->>'date' as s1_date, 
       d2_json->'stage1'->>'status' as s1_status, 
       d2_json->'stage1'->>'uid' as s1_uid,  
       d2_json->'stage2'->>'date' as s2_date, 
       d2_json->'stage2'->>'status' as s2_status, 
       d2_json->'stage2'->>'uid' as s2_uid 
FROM json_data_table AS jd;

How to join table to get expected table as shown in image?

Tags:

2

Answers


  1. 0

    You just join it and use the expression to extract the uid in the join condition.
    And since you need to different sets of user_data you need to join the table twice.
    And finally since uid is a number and JSON consists of text you have to convert between the two.
    I opted to do the conversion on the JSON side, since this enables usage of an index for user_data which seems reasonable to exist in the real use case.

    SELECT d1_json->>'stage1' as stage1, 
       d1_json->>'stage2' as stage2, 
       d2_json->'stage1'->>'date' as s1_date, 
       d2_json->'stage1'->>'status' as s1_status, 
       d2_json->'stage1'->>'uid' as s1_uid,  
       s1ud.username as s1_username,
       d2_json->'stage2'->>'date' as s2_date, 
       d2_json->'stage2'->>'status' as s2_status, 
       d2_json->'stage2'->>'uid' as s2_uid,  
       s2ud.username as s2_username 
FROM json_data_table AS jd
join user_data s1ud
on s1ud.uid = to_number( d2_json->'stage1'->>'uid', '999')
join user_data s2ud
on s2ud.uid = to_number( d2_json->'stage2'->>'uid', '999');

    DB-Fiddle to play with.

    Login or Signup to reply.
  2. 0

    Try this:

    SELECT 
  d1_json ->> 'stage1' as stage1, 
  d1_json ->> 'stage2' as stage2, 
  d2_json -> 'stage1' ->> 'date' as s1_date, 
  d2_json -> 'stage1' ->> 'status' as s1_status, 
  d2_json -> 'stage1' ->> 'uid' as s1_uid, 
    s1_user.username as s1_username, 
  d2_json -> 'stage2' ->> 'date' as s2_date, 
  d2_json -> 'stage2' ->> 'status' as s2_status, 
  d2_json -> 'stage2' ->> 'uid' as s2_uid, 
  s2_user.username as s2_username 
FROM 
  json_data_table AS jd 
  JOIN user_data s2_user ON s2_user.uid :: TEXT = d2_json -> 'stage2' ->> 'uid' :: TEXT 
  JOIN user_data s1_user ON s1_user.uid :: TEXT = d2_json -> 'stage1' ->> 'uid' :: TEXT;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search