extracting elements out of a json object inside an postgresql table

Question

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?

data->customer->>ppan as ppan <-- was another table with the object customer — George Popadalis
– George Popadalis, Commented May 8, 2020 at 5:36

user330315 · Accepted Answer · 2020-05-08 05:47:15Z

1

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

answered May 8, 2020 at 5:47

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

George Popadalis Over a year ago

Thank you!!! that makes perfect sense.. been looking at every single example on the web and missed the point..

Collectives™ on Stack Overflow

extracting elements out of a json object inside an postgresql table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related