Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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 SCHEMA

CREATE TABLE

INSERT 0 2

empid
json_data

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 2

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;

CREATE TABLE

INSERT 0 3

deptid
json_data

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 3

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'%'
   
ERROR:  syntax error at or near "DepartmentId"
LINE 6: ...ta::json->>'dv_DeptId' like '%e.json_data::json->'Department...
                                                             ^

empid	json_data
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"}}

deptid	json_data
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"}}