Postgresql - JSON data filtering for joininbg 2 tables using like operator

Question

I have JSON data in 2 tables of Postgresql 13. I am trying to join them using the like operator. But since the data in the field is JSON I am getting a syntax error.

ERROR: syntax error at or near "DepartmentId" LINE 6: ...ta::json->>'dv_DeptId' like '%e.json_data::json->'Department...

SQL FIDDLE for reference

Below is my query:

CREATE SCHEMA dbo;

CREATE TABLE dbo.Emp
(
  EmpId varchar(50),
  json_data varchar
);


INSERT INTO dbo.Emp (EmpId,json_data) values ('E1', '{"EmpId":{"value":"E1","display_value":"E1"},"DepartmentId":{"value":"D1","display_value":"D1"}}')
  ,('E2', '{"EmpId":{"value":"E2","display_value":"E2"},"DepartmentId":{"value":"D2","display_value":"D2"}}');
  
select * from dbo.Emp;

CREATE TABLE dbo.Dept
(
  DeptId varchar(50),
  json_data varchar

);

INSERT INTO dbo.Dept (DeptId, json_data) values ('D1', '{"DeptId":"D1","dv_DeptId":"D1","DeptName":"IT","dv_DeptName":"IT"}}')
  ,('D2', '{"DeptId":"D2","dv_DeptId":"D2","DeptName":"Marketing","dv_DeptName":"Marketing"}}')
  ,('D3', '{"DeptId":"D3","dv_DeptId":"D3","DeptName":"HR","dv_DeptName":"HR"}}');

SELECT * FROM dbo.Dept;

select  
    e.json_data::json->'DepartmentId' ->> 'value' as "Emp_DepartmentId"
  , d.json_data::json->>'dv_DeptId' as "Dept_DepartmentId"
    from dbo.Emp e
    LEFT JOIN dbo.Dept as d
    ON d.json_data::json->>'dv_DeptId' like '%e.json_data::json->'DepartmentId' ->> 'value'%' -- throws syntax error

Expected Output

Emp_DepartmentId  Dept_DepartmentId
D1                   D1
D2                   D2

Answer 1

You can construct your like statement a little bit differently by concatenating '%' to it and it will work.

dbfiddle

select  
   e.json_data::json->'DepartmentId' ->> 'value' as "Emp_DepartmentId"
   , d.json_data::json ->> 'dv_DeptId' as "Dept_DepartmentId"
    from dbo.Emp e
    LEFT JOIN dbo.Dept as d
    ON d.json_data::json ->> 'dv_DeptId' like '%' || cast(e.json_data::json->'DepartmentId' ->> 'value' as varchar) || '%'