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

Postgresql – How to left join using select form

I’m tryng to use a join function in my SQL request.

I have two table :

tbl_jsontesting

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| id | data                                                                                                                                                                               | name       |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1  | {"complexProperties":[{"properties":{"key":"Registred","Value":"123456789"}},{"properties":{"key":"Urgency","Value":"Total"}},{"properties":{"key":"ImpactScope","Value":"All"}}]} | Some Text  |
| 2  | {"complexProperties":[{"properties":{"key":"Registred","Value":"123456788"}},{"properties":{"key":"Urgency","Value":"Total"}},{"properties":{"key":"ImpactScope","Value":"All"}}]} | Some Text2 |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

tbl_registred

----------------------
| id        | name   |
----------------------
| 123456789 | Source |
| 123456788 | Cars   |
----------------------

The id from the table tbl_registred is linked to the id contained in table tbl_jsontesting in the column data when key is set to Registred

My current query :

select (j -> 'properties' ->> 'Value') as "Registred", "registred_name"
from 
(
 select json_array_elements("data"::json -> 'complexProperties') as j, tbl_registred.name as "registred_name"
 from tbl_jsontesting
LEFT JOIN tbl_registred ON tbl_jsontesting.id = tbl_registred.id
) as arrj
where j -> 'properties' ->> 'key' = 'Registred';

Result :

------------------------------
| Registred | registred_name |
------------------------------
| 123456788 | null           |
| 123456789 | null           |
------------------------------

My expected output :

------------------------------
| Registred | registred_name |
------------------------------
| 123456788 | Cars           |
| 123456789 | Source         |
------------------------------

My fidle :
https://www.db-fiddle.com/f/5Jvq4SXUpBvJsY7H3G13xm/1

Tags:

2

Answers


  1. 0

    In your data there is not match between tbl_registred.id and tbl_jsontesting.id so your JOIN give no result.

    According to your data I guess you want to join tbl_registred.id with tbl_jsontesting > complexProperties > properties > Value where the key is ‘Registred’
    This is a long path to find the desired object so I will decompose the Json

    WITH fuly_decomposed_tbl_jsontesting as (
  SELECT * , (JsonSubComplexProp->>'key') as key, (JsonSubComplexProp->>'Value') as value
  FROM (
    SELECT *,
        (
            json_array_elements(
                (j.data::json->'complexProperties')::json
            )::json->'properties'
        )::json as JsonSubComplexProp
     FROM tbl_jsontesting j
  ) decomposed_tbl_jsontesting
)
SELECT fjson.id, fjson.data, fjson.name, fjson.key ,fjson.value, tr.id, tr.name
FROM fuly_decomposed_tbl_jsontesting fjson
LEFT JOIN tbl_registred tr on tr.id::text = fjson.value
WHERE key='Registred'

    you probably can do shorter but this way work and with the explosion of the json you can do what you want

    result in your fiddle :

    
| id  | data                                                                                                                                                                               | name       | key       | value     | id        | name   |
| --- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ---------- | --------- | --------- | --------- | ------ |
| 1   | {"complexProperties":[{"properties":{"key":"Registred","Value":"123456789"}},{"properties":{"key":"Urgency","Value":"Total"}},{"properties":{"key":"ImpactScope","Value":"All"}}]} | Some Text  | Registred | 123456789 | 123456789 | Source |
| 2   | {"complexProperties":[{"properties":{"key":"Registred","Value":"123456788"}},{"properties":{"key":"Urgency","Value":"Total"}},{"properties":{"key":"ImpactScope","Value":"All"}}]} | Some Text2 | Registred | 123456788 | 123456788 | Cars   |
    Login or Signup to reply.
  2. 0

    You can convert the array elements into rows and then join on the Value property:

    select rg.id, rg.name, jt.name
from tbl_jsontesting jt
  cross join jsonb_array_elements(jt.data::jsonb -> 'complexProperties')  as p(props)
   join tbl_registred rg 
     on rg.id::text = (p.props -> 'properties' ->> 'Value')
    and p.props -> 'properties' ->> 'key' = 'Registred'
;

    Another option is to use a JSON path query to get the Value for a specific key

    select rg.id, rg.name, jt.name
from tbl_jsontesting jt
   join tbl_registred rg 
     on rg.id::text = (jsonb_path_query_first(jt.data::jsonb, '$.complexProperties[*].properties ? (@.key == "Registred").Value') #>> '{}')

    As there is no direct cast from jsonb to text we must use the somewhat ugly hack #>> '{}' to convert it to a text value.

    Alternatively you can convert the id column to a JSON scalar in the join condition:

    on to_jsonb(rg.id::text) = jsonb_path_query_first(jt.data::jsonb, '$.complexProperties[*].properties ? (@.key == "Registred").Value')

    Note that jsontesting.data should be defined as jsonb to avoid the annoying and costly cast.

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

Back To Top
Search