2

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 :

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)

Result :

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

Expected result :

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

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

2
  • According to fiddle, "Query Error: error: invalid input syntax for type json"
    – jjanes
    Commented Sep 27, 2022 at 13:49
  • Yes, it's beacause of the last row of the table tbl_jsontesting, data column is empty. If commented out : db-fiddle.com/f/5Jvq4SXUpBvJsY7H3G13xm/8
    – executable
    Commented Sep 27, 2022 at 13:50

1 Answer 1

1

You need to do a left join to your function call. Since a left join requires an ON condition, you can just use ON true. That is a bit weird, but a commonly needed trick with lateral joins.

LEFT join jsonb_array_elements(nullif(jt.data,'')::jsonb -> 'complexProperties') as p(props) ON true

See https://www.db-fiddle.com/f/76ew1fK9D5V3vopbGUuYxh/1

5
  • I don't see where I need to place or replace in my SQL request, possible to have a fiddle or full request ?
    – executable
    Commented Sep 27, 2022 at 14:15
  • added a fiddle.
    – jjanes
    Commented Sep 27, 2022 at 14:18
  • Thanks ! but it output only 3 rows. The same result as my original request. i.imgur.com/jg8vahP.png . I think you mistaken the link because there is no such left join
    – executable
    Commented Sep 27, 2022 at 14:19
  • Sorry I guess didn't save my changes. New link should be fixed now.
    – jjanes
    Commented Sep 27, 2022 at 14:33
  • Perfect, it's working ! Thanks for the tips for ON true on join
    – executable
    Commented Sep 27, 2022 at 14:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.