Сортировка внутри JSON (sql, Postgres)

#sql #json #postgresql

Есть таблица в Postgres, как на картинке. Формат данных в before и after - JSON.
Необходимо сравнить данные из before и after внутри zone_limitation. Основная проблема
заключается в том, что данные могут иметь разную сортировку, а при разной сортировке
такие JSON-ячейки будут восприниматься, как разные, даже если будут иметь одинаковые
цифры внутри.
    

Ответы

Ответ 1

в postgresql есть операторы @> и <@ для jsonb 

эти операторы проверяют структурного вхождение одного jsonb в другой (не взирая на
последовательность ключей), т.о. если вам нужно проверить эквивалентность, то можно
использовать комбинацию этих операторов, например:

with t as (
  select 
    "id",
    "before"#>'{"custom_targeting","include","zone_limitation"}' as "b",
    "after"#>'{"custom_targeting","include","zone_limitation"}' as "a"
  from "test"
)
select 
  t.id "id",
  (t.a @> t.b and t.a <@ t.b)::boolean "Checker"
from t 
order by 1


*данный ответ базируется на dbfiddle от 2SRTVF, см. оригинальный ответ:  https://ru.stackoverflow.com/a/858064/265453


Ответ 2

create table "test" (
  "id" int,
  "before" jsonb,
  "after" jsonb
);






insert into "test" ("id","before","after") values
(1,'{"custom_targeting":{"include":{"zone_limitation":["1","2"]}}}','{"custom_targeting":{"include":{"zone_limitation":["2","1"]}}}'),
(2,'{"custom_targeting":{"include":{"zone_limitation":["1","3"]}}}','{"custom_targeting":{"include":{"zone_limitation":["1","5"]}}}');






with t0 as
(
  select 
    "id",
    json_array_elements(("before"#>'{"custom_targeting","include","zone_limitation"}')::json)::text "lz"
  from "test"
),
t1 as (
  select 
    "id",
    json_array_elements(("after"#>'{"custom_targeting","include","zone_limitation"}')::json)::text "lz"
  from "test"
)
select 
  case when t0."id" is null then t1."id" else t0."id" end "id",
  (min(case when t0."id" is null or t1."id" is null then 0 else 1 end))::boolean
"checher"
from t0 
 full join t1 on t0."id" = t1."id" and t0."lz" = t1."lz"
group by 1
order by 1



id | checher
-: | :------
 1 | t      
 2 | f      



db<>fiddle here


Ответ 3

Не совсем понял, при чём тут сортировка. Вы пишете, что надо сравнить поля в пределах
одной записи.  
Вообще говоря, такая таблица нарушает, насколько помню, даже первую нормальную форму
— элементарность столбцов. Разумеется, никакой SQL-сервер с такими столбцами внятно
работать не умеет.  
Можно, например, вынести из строк информацию, с которой вы намерены работать, в дополнительные
столбцы. Например, коли уж вам нужен zone_limitation, сдублировать (либо вынести) в
отдельные столбцы.  
Можно написать свой тип, написать к нему функций и операций.