Re: Extracting data from jsonb array?

From: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
To: Steve Baldwin <steve(dot)baldwin(at)gmail(dot)com>
Cc: Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com>, PG-General Mailing List <pgsql-general(at)postgresql(dot)org>
Subject: Re: Extracting data from jsonb array?
Date: 2020-12-07 23:22:19
Message-ID: CAKFQuwaxB6bK_CSJaWM=OXjXya7BaV+3Pwpp9e4dPPSfB0CSrQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Mon, Dec 7, 2020 at 4:13 PM Steve Baldwin <steve(dot)baldwin(at)gmail(dot)com>
wrote:

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

Not so much..."Charges" is an array so "->>" doesn't do anything useful.

The OP needs to use "json_array_elements" to navigate past the array and
get to the next layer of the json where ->>'Name' will then work.

For v12 and newer readers, SQL/JSON Path should probably be used instead.

David J.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ken Tanzer 2020-12-07 23:48:39 Re: Extracting data from jsonb array?
Previous Message Ken Tanzer 2020-12-07 23:18:00 Re: Extracting data from jsonb array?