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 '%' || cast(e.json_data::json->'DepartmentId' ->> 'value' as varchar)|| '%'
   
Emp_DepartmentId
Dept_DepartmentId

D1
D1

D2
D2

SELECT 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"}}

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