find values of json array using postgresql query

Question

This is my JSON value

"order": {
        "items": [{
            "amount": 30000,
            "product_name": "product A",
            "quantity": 3,
         }, {
            "amount": 1000,
            "product_name": "product B",
            "quantity": 1,
        }],
        "currency": "USD"
    }

I want get values of amount from the array using mysql queries. I tried select json_extract_path_text('order','items') but it fetches whole value

 [{
            "amount": 30000,
            "product_name": "product A",
            "quantity": 3,
         }, {
            "amount": 1000,
            "product_name": "product B",
            "quantity": 1,
        }]

Can you help me?

Does MySQL support querying JSON? Didn't you mean PostreSQL? — Mosty Mostacho
– Mosty Mostacho, Commented Dec 5, 2013 at 5:10
@ Mosty Mostacho Thanks for notify this. I made a great mistake. You are right Its postgresql — Inaccessible
– Inaccessible, Commented Dec 5, 2013 at 5:44

roman · Accepted Answer · 2013-12-05 14:19:49Z

1

You can use json_array_elements() function:

select
    (value->>'amount')::int as amount
from json_array_elements(
    '{"order":
         {  "items": [
                {"amount": 30000,"product_name": "product A", "quantity": 3},
                {"amount": 1000,"product_name": "product B","quantity": 1}
            ],
            "currency": "USD"}
     }'::json->'order'->'items'
)

answered Dec 5, 2013 at 14:19

roman

118k30 gold badges205 silver badges209 bronze badges

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

Collectives™ on Stack Overflow

find values of json array using postgresql query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related