Postgres + JSON объект в массив

Question

Я хотел бы знать, возможно ли "объединить" объект json в массив json для итерации по смешанному набору данных.

У меня есть два ряда, которые выглядят как

{Data:{BASE:{B1:0,color:green}}}
{Data:{BASE:[{B1:1,color:red},{B1:0,color:blue}]}}

Я хотел бы извлечь B1 val из всех этих строк, но я немного заблокирован:)

Моей первой попыткой был json_extract_array, но он потерпел неудачу в 1-й строке (не в массиве). Тогда моя вторая попытка была json_array_length с регистром, но это не удалось в 1-й строке (не массив)

Могу ли я справиться с этой ситуацией каким-либо образом? По сути, мне нужно извлечь все строки, где B1 > 0, в одном из массива json (или объекта) и, возможно, вернуть узел, содержащий B1 > 0.

Answer 1

Ваша главная проблема - вы смешиваете типы данных под json -> 'Data' -> 'BASE' путь, который не может быть легко обработан. Я мог бы придумать решение, но вы должны исправить свою схему, например. содержать только массивы на этом пути.

with v(j) as (
  values (json '{"Data":{"BASE":{"B1":0,"color":"green"}}}'),
         ('{"Data":{"BASE":[{"B1":1,"color":"red"},{"B1":0,"color":"blue"}]}}')
)
select j, node
from v,
lateral (select j #> '{Data,BASE}') b(b),
lateral (select substring(trim(leading E'\x20\x09\x0A\x0D' from b::text) from 1 for 1)) l(l),
lateral (select case
  when l = '{' and (b #>> '{B1}')::numeric > 0 then b
  when l = '[' then (select e from json_array_elements(b) e where (e #>> '{B1}')::numeric > 0 limit 1)
  else null
end) node(node)
where node is not null

SQLFiddle

Answer 2

Чтобы вернуть строки, где хотя бы один объект имеет B1 > 0

select *
from t
where true in (
    select (j ->> 'B1')::int > 0
    from json_array_elements (json_column -> 'Data' -> 'BASE') s (j)
)

Answer 3

С небольшой помощью других ответов здесь я сделал это:

with v(j) as (
  values (json '{"Data":{"BASE":{"B1":0,"color":"green"}}}'),
         ('{"Data":{"BASE":[{"B1":1,"color":"red"},{"B1":0,"color":"blue"}]}}')
)
select j->'Data'->'BASE'->>'B1' as "B1"
from v 
where j->'Data'->'BASE'->>'B1' is not null
union all
select json_array_elements(j->'Data'->'BASE')->>'B1'
from v
where j->'Data'->'BASE'->>'B1' is null

Разделил его на два запроса, один из которых просто извлекает одно значение, если есть только один, и другой, который разворачивает массив, если их несколько, используя возвращаемый PostgreSQL null если вы попросите текст чего-то, что является массивом. Затем я просто объединил результат двух запросов, в результате чего:

 ------
|  B1  |
 ------
|  0   |
|  1   |
|  0   |