How to create table-like structure in json

Question

I need to put below 'table' into a json, where values would be as easily accessible as from table. Here's the small sample and simple example query of usage.

    with prizes as (
            select t.stake, tickets_no, t.prize 
            from (values 
              (400, 5, 10)
            , (1000, 10, 25)
            , (2000, 50, 70)
            ) t
            (stake, tickets_no, prize) 
            order by 1,2 
        )

select max(prize) from prizes 
where 1200 >= stake
and 27 >= tickets_no

I want to put it in some kind of this structure:

https://www.db-fiddle.com/f/pS5vR4CM8Y6sjsVXkw7XUY/1

create table promotions 
(id integer,
details jsonb);

insert into promotions
values
(1, '{"name": "promo1", "rules":[/* it should be an array here or somehow different? */]}');

Abelisto · Accepted Answer · 2020-04-09 20:58:07Z

1

It could be simpler if you reorganize your JSON structure a bit:

insert into promotions
    values
    (1, '{"name":"promo1","rules":[{"stake":100,"tickets_no":10,"prize":200},{"stake":500,"tickets_no":30,"prize":700},{"stake":1000,"tickets_no":80,"prize":1200}]}');

create type t_rule as (prize int, stake int, tickets_no int);

select id, t.*
from promotions, jsonb_populate_recordset(null::t_rule, details->'rules') as t;

 id | prize | stake | tickets_no 
----+-------+-------+------------
  1 |   200 |   100 |         10
  1 |   700 |   500 |         30
  1 |  1200 |  1000 |         80

select max(prize)
from promotions, jsonb_populate_recordset(null::t_rule, details->'rules') as t
where stake < 1111;

 max  
------
 1200

answered Apr 9, 2020 at 20:58

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

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

Comments

sh4rkyy · Accepted Answer · 2020-04-09 20:17:34Z

0

I managed to do this:

 create table promotions 
    (id integer,
    details jsonb);

    insert into promotions
    values
    (1, '{"name": "promo1", "rules":{"stake":[100,500,1000], "tickets_no":[10,30,80], "prize":[200,700, 1300]}

     }');

+ simple query with operation of values:

    with promo as (select jsonb_array_elements(details -> 'rules' -> 'stake')::text::integer as stake,
    jsonb_array_elements(details -> 'rules' -> 'tickets_no')::text::integer as bets_no,
    jsonb_array_elements(details -> 'rules' -> 'prize')::text::integer as prize
    from promotions  )

    select max(prize) from promo where 1111 > stake

answered Apr 9, 2020 at 20:17

sh4rkyy

4226 silver badges23 bronze badges

Collectives™ on Stack Overflow

How to create table-like structure in json

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related