How to cross join with json and get all data-CodePudding

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

I have two table :

tbl_jsontesting

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| id | data                                                                                                                                                                               | description |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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  |
| 3  | {"complexProperties":[{"properties":{"key":"Urgency","Value":"Total"}},{"properties":{"key":"ImpactScope","Value":"All"}}]}                                                        | Some Text3  |
| 4  | {}                                                                                                                                                                                 | Some Text4  |
| 5  | {"complexProperties":[]}                                                                                                                                                           | Some Text5  |
| 6  |                                                                                                                                                                                    | Some Text6  |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

tbl_registred

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

My current query :

select jt.id, rg.id as id_registred, rg.name, jt.description
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'
;

Result :

--------------------------------------------
| id | id_registred | name   | description |
--------------------------------------------
| 2  | 123456788    | Cars   | Some Text2  |
| 1  | 123456789    | Source | Some Text   |
--------------------------------------------

Expected result :

--------------------------------------------
| id | id_registred | name   | description |
--------------------------------------------
| 6  |              |        | Some Text6  |
| 5  |              |        | Some Text5  |
| 4  |              |        | Some Text4  |
| 3  |              |        | Some Text3  |
| 2  | 123456788    | Cars   | Some Text2  |
| 1  | 123456789    | Source | Some Text   |
--------------------------------------------

Fiddle : https://www.db-fiddle.com/f/5Jvq4SXUpBvJsY7H3G13xm/5

CodePudding user response：

in your fiddle colunm tbl_jsontesting.description is named name :)

other than that you can do a full join in place of the second join to keep all the line ;

then add the line whitout id_registred to your first result :

WITH all_line as (
  select jt.id, rg.id as id_registred, rg.name, jt.description
  from tbl_jsontesting jt
  cross join jsonb_array_elements(jt.data::jsonb -> 'complexProperties') as p(props)
  LEFT join tbl_registred rg 
    on rg.id::text = (p.props -> 'properties' ->> 'Value')
    and p.props -> 'properties' ->> 'key' = 'Registred'
),line_registred as (
  SELECT *
  FROM all_line
  WHERE id_registred IS NOT NULL
)
SELECT * FROM line_registred
UNION ALL
SELECT distinct * --distinct because of line generate by sub properties in json
   FROM all_line
   WHERE id NOT IN (SELECT id FROM line_registred)

probably not the shortest way, but you have what you excpected :

| id  | id_registred | name   | description |
| --- | ------------ | ------ | ----------- |
| 1   | 123456789    | Source | Some Text   |
| 2   | 123456788    | Cars   | Some Text2  |
| 3   |              |        | Some Text3  |