milkcrate_prod Database

Parameters

Name	Type	Mode
id_node	bigint	OUT
id_community	integer	OUT
created_at	timestamp with time zone	OUT
updated_at	timestamp with time zone	OUT
key	text	OUT
value	text	OUT
uuid_key	text	OUT
uuid_value	text	OUT

Definition

begin
return query

select x.id_node, x.id_community, x.created_at, x.updated_at,
        (select label from "field" where uuid = x.key::UUID) as key ,
        x.value as value,
        x.key as uuid_key,
        x.uuid_value

from (
    select nv.id_node, nv.id_community, nv.created_at, nv.updated_at, field_key as key, field_value as value, null as uuid_value
    from node_values nv
    cross join jsonb_each_text(nv.attributes) as t(field_key, field_value)
    where t.field_value not like '[%]'

union all

select nv.id_node, nv.id_community, nv.created_at, nv.updated_at, field_key as key,
    case when fo.name is null then field_option_key
        else fo.name
        end as value,
    case when fo.name is null then null
        else field_option_key
        end as value
    from node_values nv
    cross join jsonb_each_text(nv.attributes) as t(field_key, field_value)
    cross join jsonb_array_elements_text(t.field_value::jsonb) a(field_option_key)
    left join field_option fo on fo.uuid::text = field_option_key
    where t.field_value like '[%]'
) as x;
end;

field_detail

Parameters

Definition