Put variable inside json_extract_path_text in postgresql query

Question

I have following select:

select json_extract_path_text(rules, 'amount', '5', 'percentage')
from promotion_rules

Sample from JSON looks like this:

{
    "amount": {

        "1": {
            "percentage": 1
        },
        "2": {
            "percentage": 3
        },
        "3": {
            "percentage_below_eq": 5,
            "percentage_above": 10,
            "price": 20
        },
        "4": {
            "percentage_below_eq": 10,
            "percentage_above": 15,
            "price": 20
        }
    }
}

I want to use values from other queries/tables/cte inside above json_extract function instead of '5' (or achieve exact effect), how it can be done?

Here's the part of code and fiddle with full data, I can't put it all here because stack tells me that my post i mostly code.

with percentages as (select pr.*, json_object_keys(rules->'amount')::INT as amount
from
promotion_rules pr
where id = 1
)
select
o.id as order_id,
json_extract_path_text(rules, 'amount', o.products_no, 'percentage') as percentage --it doesn't work this way, either with brackets
from orders o
join percentages p on p.amount = o.products_no

https://www.db-fiddle.com/f/oSQ3eW2G3kHgr3xvpHLw9Q/0

Yes I meant value from some table, or cte - corrected main post — sh4rkyy
– sh4rkyy, Commented Jan 29, 2020 at 8:44
No, it doesn't work this way, if I put it in brackets It would treat is as potential key (path) in json, without brackets it generates an error: ERROR: function json_extract_path_text(json, unknown, integer, unknown) does not exist — sh4rkyy
– sh4rkyy, Commented Jan 29, 2020 at 8:54

user330315 · Accepted Answer · 2020-01-29 09:31:09Z

3

json_extract_path expects a list of text parameters.

If you want to use a column that's not text you need to cast it:

json_extract_path_text(rules, 'amount', o.products_no::text, 'percentage')

answered Jan 29, 2020 at 9:31

user330315

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

Collectives™ on Stack Overflow

Put variable inside json_extract_path_text in 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