Re: Extracting data from jsonb array?

Try:

select _message_body->'Charges'->>'Name' from ...

Steve

On Tue, Dec 8, 2020 at 9:58 AM Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com> wrote:

> Hello. This is probably simple, but I'm having a hard time making use of
> some json data, and hoping someone can help.
>
> Given some data that looks like this (I added a couple of carriage
> returns for readability):
>
> SELECT _message_body->'Charges' FROM message_import_court_case where
> _message_exchange_id=1296;
>
>
> ?column?
>
>
>
>
> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> ------------------------------------------------------------------------------------------------------------------
> [
> {"NCIC": {"Code": null, "Name": null, "Behavior": null}, "Name":
> "Possession Of Burglary Tools", "OffenseCodes": "9A52030;9A52060",
> "ClassSeverity": {"Code": "M|GM", "Description": null}},
> {"NCIC": {"Code": null, "Name": null, "Behavior": null}, "Name": "Burglary
> In The Second Degree (Commercial)", "OffenseCodes": "9A52030",
> "ClassSeverity": {"Code": "F|B", "Description": null}}
> ]
>
> How can I extract the two "Name" elements? (i.e.:
>
> Possession of Burglary Tools
> Burglary In The Second Degree (Commercial)
>
> This is with 9.6.20. Thanks in advance!
>
> Ken
>
>
>
>
>
>
> --
> AGENCY Software
> A Free Software data system
> By and for non-profits
> *http://agency-software.org/ <http://agency-software.org/>*
> *https://demo.agency-software.org/client
> <https://demo.agency-software.org/client>*
> ken(dot)tanzer(at)agency-software(dot)org
> (253) 245-3801
>
> Subscribe to the mailing list
> <agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
> learn more about AGENCY or
> follow the discussion.
>