How to cross join with json and get all data

Question

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

Answer 1

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  |

---

next to your edit :

line 6 in is not valid ('' is not json)

line 4 and 5 doesn't have 'properties' so select id, jsonb_array_elements(data::jsonb -> 'complexProperties') FROM tbl_jsontesting does'nt return anything for that 2 lines

you could do the same tricks that we done for line not 'Registred' with matched line then all line give you some thing like :

**Query #1**

WITH line_with_properties as (
        select id,description, jsonb_array_elements(data::jsonb -> 'complexProperties') as props
        FROM tbl_jsontesting 
    ),all_json_line as (
        SELECT id,description, props
        FROM line_with_properties
        UNION ALL 
        SELECT id,description, '{}' as props
        FROM tbl_jsontesting
        WHERE id NOT IN (SELECT id from line_with_properties)
    ), all_line as(
      select ajl.id, rg.id as id_registred, rg.name, ajl.description
      from all_json_line ajl
      LEFT join tbl_registred rg 
        on rg.id::text = (ajl.props -> 'properties' ->> 'Value')
        and ajl.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 * 
       FROM all_line
       WHERE id NOT IN (SELECT id FROM line_registred)
    order by id;

and again what you expected tested in your fiddle (without line 6):

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