postgres convert json array to columns

Question

I'm working in postgres 9.6 and still getting my head around json

i have a column with a json object that is an array of numbers that represent recurrence frequency and the days of the week.

{"every":"1","weekdays":["1"]}
{"every":"1","weekdays":["1","3"]}
{"every":"1","weekdays":["1","2","3","4","5"]}

ROW1 -[1]          : MON  
ROW2 -[1,3]        : MON , WED  
ROW3 -[1,2,3,4,5]  : MON , TUE , WED , THU , FRI

I want to expand these into columns such that:

|ROW- |MON | TUE| WED|THU|FRI|  
------------------------------
|ROW1 |Y   |N   |N   |N  |N  |  
|ROW2 |Y   |N   |Y   |N  |N  |  
|ROW3 |Y   |Y   |Y   |Y  |Y  |

I can get the elements out using jsonb_array_elements(pattern) but then what?

i thought to use the 'contains' expression to build each column

pattern @> '{1}', pattern @> '{2}' etc but I couldn't construct an object that would give a hit

Can you please also add your json to the question? It could make it easier to understand. — Pitto
– Pitto, Commented Mar 22, 2018 at 9:39

klin · Accepted Answer · 2018-03-22 10:04:49Z

3

Example data:

create table my_table(id serial primary key, pattern jsonb);
insert into my_table (pattern) values
('{"weekdays": [1]}'),
('{"weekdays": [1, 3]}'),
('{"weekdays": [1, 2, 3, 4, 5]}');

You can use the operator @> in this way:

select 
    id,
    pattern->'weekdays' @> '[1]' as mon,
    pattern->'weekdays' @> '[2]' as tue,
    pattern->'weekdays' @> '[3]' as wed,
    pattern->'weekdays' @> '[4]' as thu,
    pattern->'weekdays' @> '[5]' as fri
from my_table

 id | mon | tue | wed | thu | fri 
----+-----+-----+-----+-----+-----
  1 | t   | f   | f   | f   | f
  2 | t   | f   | t   | f   | f
  3 | t   | t   | t   | t   | t
(3 rows)

edited Mar 22, 2018 at 10:04

answered Mar 22, 2018 at 9:59

klin

123k15 gold badges240 silver badges262 bronze badges

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

Comments

Darryl · Accepted Answer · 2018-03-22 10:00:15Z

0

It seems i was on the right track with 'contains' but i had confused myself about what was in the array. I should have been looking for a string not a number

, bookings.pattern->'weekdays' @> '"1"'::jsonb

Thanks to Pitto for the prompt to put the outer json in the question which made it obvious

answered Mar 22, 2018 at 10:00

Darryl

131 silver badge5 bronze badges

1 Comment

Darryl Over a year ago

i hadn't noticed previously but the outer json is {"every":"1","weekdays":["1","4","5"]} but when i query the array 'weekdays' i get [1,4,5] which threw me off a bit

Collectives™ on Stack Overflow

postgres convert json array to columns

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related