How to left join using select form-CodePudding

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

CodePudding user response：

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   |

CodePudding user response：

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.