Как сделать выбрку по массиву JSON в Postgresql 9.6?

Question

[Евгений]

Есть таблица с полем fields

в нем примерно такие значения

[
  {
    "type": "radio-group",
    "required": true,
    "label": "Status in Canada",
    "name": "radio-group-1673974334336-0",
    "custom_twine": "custom_85",
    "values": [
      {
        "label": "Citizen",
        "value": "1",
        "selected": false
      },
      {
        "label": "Permanent Resident",
        "value": "3",
        "selected": false
      },
      {
        "label": "Study Permit",
        "value": "12",
        "selected": false
      },
      {
        "label": "Other Status",
        "value": "98",
        "selected": false
      }
    ]
  },
  {
    "type": "header",
    "label": "Transportation Information",
    "className": "h2"
  },
  {
    "type": "paragraph",
    "label": "Is school bus transportation required for this student?  (Additional transportation fee applies)"
  },
  {
    "type": "radio-group",
    "required": true,
    "label": "Transportation",
    "name": "radio-group-1673190453587-0",
    "custom_twine": "custom_38",
    "values": [
      {
        "label": "No",
        "value": "NO",
        "selected": false
      },
      {
        "label": "Yes - morning only",
        "value": "AM",
        "selected": false
      },
      {
        "label": "Yes - afternoon only",
        "value": "PM",
        "selected": false
      },
      {
        "label": "Yes - Morning and Afternoon",
        "value": "BOTH",
        "selected": false
      }
    ]
  },
]

Не могу никак создать запрос, чтобы получить значение поля name, из секции где custom_twine: custom_38. В строке может быть только одна секция с custom_38.

Answer 1

[Melkij]

select (select j->>'name' from jsonb_array_elements(jsonb_field) as j where j->>'custom_twine' = 'custom_38') from tablename

Comments

[Евгений]

Спасибо