Как вытащить значение атрибута из json , при большой вложенности атрибутов в PostgreSQL
Подскажите пожалуйста, как select -от достать из атрибута "Id": "Name"
вот это "Value": "% - 0,0040мм",
Не смог найти гуглом подходящий метод.
Переход по средствам :
json_array_elements(qualityindicators :: json -> 'Elements') -> 1 -> 'Attributes' -> 4 -> 'Value' -> 'Attributes' -> 1 ->'Value' -> 'Value' qualityindicators
выдает значение NULL
{
"ElementTypeReference": "s:PolyusMesQm/Types/QualityIndicatorResult",
"IsDictionary": false,
"Elements": [
{
"Attributes": [
{
"Value": {
"Value": 120,
"TypeReference": "s:sys/core/types/Float"
},
"Id": "NumericResult"
},
{
"Value": {
"Value": "120",
"TypeReference": "s:sys/core/types/String"
},
"Id": "FormattedResult"
},
{
"Value": {
"Value": "120",
"TypeReference": "s:sys/core/types/String"
},
"Id": "EntryResult"
},
{
"Value": {
"Attributes": [
{
"Value": {
"Value": "744",
"TypeReference": "s:sys/core/types/String"
},
"Id": "Id"
},
{
"Value": {
"Value": "%",
"TypeReference": "s:sys/core/types/String"
},
"Id": "SymbolRus"
},
{
"Value": {
"TypeReference": "s:sys/core/types/Void"
},
"Id": "SymbolEng"
},
{
"Value": {
"TypeReference": "s:sys/core/types/Void"
},
"Id": "CodeRus"
},
{
"Value": {
"TypeReference": "s:sys/core/types/Void"
},
"Id": "CodeEng"
},
{
"Value": {
"Value": "o:site/app/RcAppProMdm/CorporateUoms",
"TypeReference": "s:sys/core/types/String"
},
"Id": "ModelReference"
},
{
"Value": {
"Value": "4ced71db75a445aab146b254bdca88ee",
"TypeReference": "s:sys/core/types/String"
},
"Id": "ElementId"
},
{
"Value": {
"Value": "% (744)",
"TypeReference": "s:sys/core/types/String"
},
"Id": "ElementCode"
},
{
"Value": {
"Value": "o:RcAppProMdm/Domains/Uom",
"TypeReference": "s:sys/core/types/String"
},
"Id": "Domain"
},
{
"Value": {
"Value": false,
"TypeReference": "s:sys/core/types/Boolean"
},
"Id": "IsNonDeletable"
},
{
"Value": {
"Value": false,
"TypeReference": "s:sys/core/types/Boolean"
},
"Id": "IsReadonly"
},
{
"Value": {
"Value": "2020-01-01T00:00:00+03:00",
"TypeReference": "s:sys/core/types/DateTime"
},
"Id": "CreationTime"
},
{
"Value": {
"TypeReference": "s:sys/core/types/Void"
},
"Id": "ExpirationTime"
}
],
"DynamicAttributes": {},
"TypeReference": "s:RcAppProMdm/Domains/Uom/Uom"
},
"Id": "Unit"
},
{
"Value": {
"Attributes": [
{
"Value": {
"TypeReference": "s:sys/core/types/Void"
},
"Id": "Code"
},
{
"Value": {
"Value": "% - 0,0071мм",
"TypeReference": "s:sys/core/types/String"
},
"Id": "Name"
},
{
"Value": {
"TypeReference": "s:sys/core/types/Void"
},
"Id": "Description"
},
{
"Value": {
"Value": true,
"TypeReference": "s:sys/core/types/Boolean"
},
"Id": "IsLocal"
},
{
"Value": {
"ElementTypeReference": "s:RcAppProMdm/Domains/Uom/Uom",
"IsDictionary": false,
"Elements": [
{
"Attributes": [
{
"Value": {
"TypeReference": "s:sys/core/types/Void"
},
"Id": "Id"
},
{
"Value": {
"Value": "%",
"TypeReference": "s:sys/core/types/String"
},
"Id": "SymbolRus"
},
{
"Value": {
"TypeReference": "s:sys/core/types/Void"
},
"Id": "SymbolEng"
},
{
"Value": {
"TypeReference": "s:sys/core/types/Void"
},
"Id": "CodeRus"
},
{
"Value": {
"TypeReference": "s:sys/core/types/Void"
},
"Id": "CodeEng"
},
{
"Value": {
"Value": "o:site/app/RcAppProMdm/CorporateUoms",
"TypeReference": "s:sys/core/types/String"
},
"Id": "ModelReference"
},
{
"Value": {
"Value": "4ced71db75a445aab146b254bdca88ee",
"TypeReference": "s:sys/core/types/String"
},
"Id": "ElementId"
},
{
"Value": {
"TypeReference": "s:sys/core/types/Void"
},
"Id": "ElementCode"
},
{
"Value": {
"Value": "o:RcAppProMdm/Domains/Uom",
"TypeReference": "s:sys/core/types/String"
},
"Id": "Domain"
},
{
"Value": {
"Value": false,
"TypeReference": "s:sys/core/types/Boolean"
},
"Id": "IsNonDeletable"
},
{
"Value": {
"Value": false,
"TypeReference": "s:sys/core/types/Boolean"
},
"Id": "IsReadonly"
},
{
"Value": {
"TypeReference": "s:sys/core/types/Void"
},
"Id": "CreationTime"
},
{
"Value": {
"TypeReference": "s:sys/core/types/Void"
},
"Id": "ExpirationTime"
}
],
"DynamicAttributes": {},
"TypeReference": "s:RcAppProMdm/Domains/Uom/Uom"
}
],
"ElementsDictionary": {},
"LookupKey": null,
"TypeReference": "a:s:RcAppProMdm/Domains/Uom/Uom"
},
"Id": "Units"
},
{
"Value": {
"TypeReference": "s:sys/core/types/Void"
},
"Id": "ModelReference"
},
{
"Value": {
"Value": "ba48fa2e64ae4e4dbf88ce8ba483ca9c",
"TypeReference": "s:sys/core/types/String"
},
"Id": "ElementId"
},
{
"Value": {
"TypeReference": "s:sys/core/types/Void"
},
"Id": "ElementCode"
},
{
"Value": {
"TypeReference": "s:sys/core/types/Void"
},
"Id": "Domain"
},
{
"Value": {
"Value": false,
"TypeReference": "s:sys/core/types/Boolean"
},
"Id": "IsNonDeletable"
},
{
"Value": {
"Value": false,
"TypeReference": "s:sys/core/types/Boolean"
},
"Id": "IsReadonly"
},
{
"Value": {
"TypeReference": "s:sys/core/types/Void"
},
"Id": "CreationTime"
},
{
"Value": {
"TypeReference": "s:sys/core/types/Void"
},
"Id": "ExpirationTime"
}
],
"DynamicAttributes": {},
"TypeReference": "s:PolyusMesQm/Domains/QmQualityIndicators/QualityIndicator"
},
"Id": "Indicator"
}
],
"DynamicAttributes": {},
"TypeReference": "s:PolyusMesQm/Types/QualityIndicatorResult"
},
{
"Attributes": [
{
"Value": {
"Value": 100,
"TypeReference": "s:sys/core/types/Float"
},
"Id": "NumericResult"
},
{
"Value": {
"Value": "100",
"TypeReference": "s:sys/core/types/String"
},
"Id": "FormattedResult"
},
{
"Value": {
"Value": "100",
"TypeReference": "s:sys/core/types/String"
},
"Id": "EntryResult"
},
{
"Value": {
"Attributes": [
{
"Value": {
"Value": "744",
"TypeReference": "s:sys/core/types/String"
},
"Id": "Id"
},
{
"Value": {
"Value": "%",
"TypeReference": "s:sys/core/types/String"
},
"Id": "SymbolRus"
},
{
"Value": {
"TypeReference": "s:sys/core/types/Void"
},
"Id": "SymbolEng"
},
{
"Value": {
"TypeReference": "s:sys/core/types/Void"
},
"Id": "CodeRus"
},
{
"Value": {
"TypeReference": "s:sys/core/types/Void"
},
"Id": "CodeEng"
},
{
"Value": {
"Value": "o:site/app/RcAppProMdm/CorporateUoms",
"TypeReference": "s:sys/core/types/String"
},
"Id": "ModelReference"
},
{
"Value": {
"Value": "4ced71db75a445aab146b254bdca88ee",
"TypeReference": "s:sys/core/types/String"
},
"Id": "ElementId"
},
{
"Value": {
"Value": "% (744)",
"TypeReference": "s:sys/core/types/String"
},
"Id": "ElementCode"
},
{
"Value": {
"Value": "o:RcAppProMdm/Domains/Uom",
"TypeReference": "s:sys/core/types/String"
},
"Id": "Domain"
},
{
"Value": {
"Value": false,
"TypeReference": "s:sys/core/types/Boolean"
},
"Id": "IsNonDeletable"
},
{
"Value": {
"Value": false,
"TypeReference": "s:sys/core/types/Boolean"
},
"Id": "IsReadonly"
},
{
"Value": {
"Value": "2020-01-01T00:00:00+03:00",
"TypeReference": "s:sys/core/types/DateTime"
},
"Id": "CreationTime"
},
{
"Value": {
"TypeReference": "s:sys/core/types/Void"
},
"Id": "ExpirationTime"
}
],
"DynamicAttributes": {},
"TypeReference": "s:RcAppProMdm/Domains/Uom/Uom"
},
"Id": "Unit"
},
{
"Value": {
"Attributes": [
{
"Value": {
"TypeReference": "s:sys/core/types/Void"
},
"Id": "Code"
},
{
"Value": {
"Value": "% - 0,0040мм",
"TypeReference": "s:sys/core/types/String"
},
"Id": "Name"
},
{
"Value": {
"TypeReference": "s:sys/core/types/Void"
},
"Id": "Description"
},
{
"Value": {
"Value": true,
"TypeReference": "s:sys/core/types/Boolean"
},
"Id": "IsLocal"
},
{
"Value": {
"ElementTypeReference": "s:RcAppProMdm/Domains/Uom/Uom",
"IsDictionary": false,
"Elements": [
{
"Attributes": [
{
"Value": {
"TypeReference": "s:sys/core/types/Void"
},
"Id": "Id"
},
{
"Value": {
"Value": "%",
"TypeReference": "s:sys/core/types/String"
},
"Id": "SymbolRus"
},
{
"Value": {
"TypeReference": "s:sys/core/types/Void"
},
"Id": "SymbolEng"
},
{
"Value": {
"TypeReference": "s:sys/core/types/Void"
},
"Id": "CodeRus"
},
{
"Value": {
"TypeReference": "s:sys/core/types/Void"
},
"Id": "CodeEng"
},
{
"Value": {
"Value": "o:site/app/RcAppProMdm/CorporateUoms",
"TypeReference": "s:sys/core/types/String"
},
"Id": "ModelReference"
},
{
"Value": {
"Value": "4ced71db75a445aab146b254bdca88ee",
"TypeReference": "s:sys/core/types/String"
},
"Id": "ElementId"
},
{
"Value": {
"TypeReference": "s:sys/core/types/Void"
},
"Id": "ElementCode"
},
{
"Value": {
"Value": "o:RcAppProMdm/Domains/Uom",
"TypeReference": "s:sys/core/types/String"
},
"Id": "Domain"
},
{
"Value": {
"Value": false,
"TypeReference": "s:sys/core/types/Boolean"
},
"Id": "IsNonDeletable"
},
{
"Value": {
"Value": false,
"TypeReference": "s:sys/core/types/Boolean"
},
"Id": "IsReadonly"
},
{
"Value": {
"TypeReference": "s:sys/core/types/Void"
},
"Id": "CreationTime"
},
{
"Value": {
"TypeReference": "s:sys/core/types/Void"
},
"Id": "ExpirationTime"
}
],
"DynamicAttributes": {},
"TypeReference": "s:RcAppProMdm/Domains/Uom/Uom"
}
],
"ElementsDictionary": {},
"LookupKey": null,
"TypeReference": "a:s:RcAppProMdm/Domains/Uom/Uom"
},
"Id": "Units"
},
{
"Value": {
"TypeReference": "s:sys/core/types/Void"
},
"Id": "ModelReference"
},
{
"Value": {
"Value": "89ac7aaaea13430e9fdb17f8de14fe12",
"TypeReference": "s:sys/core/types/String"
},
"Id": "ElementId"
},
{
"Value": {
"TypeReference": "s:sys/core/types/Void"
},
"Id": "ElementCode"
},
{
"Value": {
"TypeReference": "s:sys/core/types/Void"
},
"Id": "Domain"
},
{
"Value": {
"Value": false,
"TypeReference": "s:sys/core/types/Boolean"
},
"Id": "IsNonDeletable"
},
{
"Value": {
"Value": false,
"TypeReference": "s:sys/core/types/Boolean"
},
"Id": "IsReadonly"
},
{
"Value": {
"TypeReference": "s:sys/core/types/Void"
},
"Id": "CreationTime"
},
{
"Value": {
"TypeReference": "s:sys/core/types/Void"
},
"Id": "ExpirationTime"
}
],
"DynamicAttributes": {},
"TypeReference": "s:PolyusMesQm/Domains/QmQualityIndicators/QualityIndicator"
},
"Id": "Indicator"
}
],
"DynamicAttributes": {},
"TypeReference": "s:PolyusMesQm/Types/QualityIndicatorResult"
}
],
"ElementsDictionary": {},
"LookupKey": null,
"TypeReference": "a:s:PolyusMesQm/Types/QualityIndicatorResult"
}
Ответы (1 шт):
Автор решения: Дмитрий Астахов
→ Ссылка
Добавлю более универсальный способ:
(SELECT Value FROM json_array_elements(ct.Value->'Attributes') as t1
WHERE (t1.Value->>'Id')='NumericResult')->'Value'->'Value' as NumericResult,
(SELECT Value FROM json_array_elements(
(SELECT Value FROM json_array_elements(ct.Value->'Attributes') as t1
WHERE (t1.Value->>'Id')='Indicator')->'Value'->'Attributes') as t2
WHERE (t2.Value->>'Id')='ElementId')->'Value'->>'Value' as Indicator_ElementId
FROM qualitymanagement_sample as t
INNER JOIN material_material as mm on mm.elementid =t.material
CROSS JOIN LATERAL json_array_elements(t.qualityindicators::json->'Elements') as ct