to_char() output as a json key isn't text? (Postgres)

Question

I have a json containing day names as keys and I'm trying to select the value from the json using today as a key, where today is Thursday. However this does not work as intended.

SELECT hoursJson->'normalHours'->'Thursday' FROM BusinessHours;

returns the hours contained in the json correctly, but

SELECT hoursJson->'normalHours'->to_char(current_date, 'Day') FROM BusinessHours;

returns nothing.

Why is to_char(current_date, 'Day') not equal to 'Thursday' when used as a json key? to_char() returns text so I don't know why these two keys aren't equal. How do I make them equal so I can use to_char() as a json key?

Thanks!

It is working on 9.4. Can you send some examples? You can check a parenthesis: SELECT jsonval->'xxx'->(func())' — Pavel Stehule
– Pavel Stehule, Commented Feb 6, 2015 at 5:54
@PavelStehule No it isn't working in 9.4, to_char blank-pads the return value when you use 'Day' so ...->to_char(current_date, 'Day') will only work properly on Wednesday (in an English locale too of course). — mu is too short
– mu is too short, Commented Feb 6, 2015 at 6:34
@muistooshort I was partially wrong - I tested a usage of function there, not tested to_char( 'Day') — Pavel Stehule
– Pavel Stehule, Commented Feb 6, 2015 at 6:48

mu is too short · Accepted Answer · 2015-02-06 05:54:41Z

2

You have a whitespace problem. From the fine to_char manual:

Day full capitalized day name (blank-padded to 9 chars)

Note the "blank-padded" part. You're not getting 'Thursday' from to_char, you're getting 'Thursday ' and those are different keys in JSON.

If you want to keep using to_char then you're going to have to deal with the trailing whitespace using something like:

...->trim(trailing ' ' from to_char(current_date, 'Day'))
...->regexp_replace(to_char(current_date, 'Day'), '\s+', '')

A quick demo of what's going on (which will work on Thursday or Friday):

http://sqlfiddle.com/#!15/2a77b/23

You might want to use different keys in your JSON so that you won't have to deal with the whitespace at all or you could write your own function to hide away the to_char and trim nastiness.

answered Feb 6, 2015 at 5:54

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

2 Comments

Teboto Over a year ago

It worked thanks! I'm using the trim method. Which one is more efficient?

mu is too short Over a year ago

@Teboto Probably trim but the difference is mostly likely too small to make any difference. Switching to day numbers in the JSON would be even better and would be locale-independent.

Collectives™ on Stack Overflow

to_char() output as a json key isn't text? (Postgres)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related