Postgresql数据过滤为使用相似操作符的joininbg 2表

Question

我在Postgresql 13的两个表中有JSON数据，我试图使用like操作符连接它们。但是由于字段中的数据是JSON，所以我得到了一个语法错误。

错误:语法错误在"DepartmentId“第6行或附近：.ta：：json->‘dv_DeptId’类‘%e.json_data：：json->’Department.

SQL小提琴供参考

以下是我的查询：

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

预期输出

Emp_DepartmentId  Dept_DepartmentId
D1                   D1
D2                   D2

Answer 1

通过将like连接到'%'，您可以构造稍微不同的'%'语句，它将工作。

[医]小提琴

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) || '%'