CODEWITHSUNDEEP
arraysjsonpostgresqljsonbpostgresql-9.6
Darryl
Darryl

Reputation: 13

postgres convert json array to columns

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

Upvotes: 1

Views: 3784

Answers (2)

klin
klin

Reputation: 121504

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)

Upvotes: 3

Darryl
Darryl

Reputation: 13

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

Upvotes: 0

Related Questions