Select or Filter JSON field dynamically by value in PostgreSQL

Question

having JSON column in table I can selected nested properties like...

SELECT '{"monday": 123}'::json->>'monday'; --> returns 123

...but if I want to select properties dynamically, it doesn't work

SELECT '{"monday": 123}'::json->>to_char(now() AT TIME ZONE 'Pacific/Yap', 'day'); --> returns null

Is is even possible to achieve?

Thank you!

S-Man · Accepted Answer · 2019-11-25 13:49:08Z

2

You need to add the FM prefix to the day keyword. to_char(..., 'day') has a fixed size, meaning after monday there are trailing spaces. The FM prefix removes them:

Click: demo:db<>fiddle

SELECT '{"monday": 123}'::json ->> to_char(now() AT TIME ZONE 'Pacific/Yap', 'FMday')

answered Nov 25, 2019 at 13:49

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

1 Comment

user330315 Over a year ago

You probably also need a lower() around the whole expression

Collectives™ on Stack Overflow

Select or Filter JSON field dynamically by value in PostgreSQL

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