CODEWITHSUNDEEP
postgresqljsonb
George Popadalis
George Popadalis

Reputation: 11

extracting elements out of a json object inside an postgresql table

I have been trying to learn how to do a select query on a table that has a jsonb variable.

basically the table is called cardtech and the variable is data

 {
"cardTransaction": [
    {
        "ppan": "482003PAYXMO3770",
        "time": "2020-03-21T00:00:00",
        "type": "205",
        "amount": {
            "ccy": "IDR",
            "value": 325105
        },

is one of the records.

in all the other tables i was able to extract data using

 data->customer->>ppan as ppan

But with this I cant seem to get past the first section and no matter what I try I can only get the full object.

I am looking for some help on how to extract the first element of cardTransaction which is ppan.

to create a new view table.

Can someone please give me a hand?

Upvotes: 1

Views: 172

Answers (1)

user330315
user330315

Reputation:

The element for the key cardTransaction is an array, so you need to access the object by index 

data -> 'cardTransaction' -> 0 ->> 'ppan'

The 'cardTransaction' -> 0 selects the first element of the array identified by the key 'cardTransaction'

Unlike native Postgres arrays, JSON arrays start with index 0

Upvotes: 1

Related Questions