jsonb.sql

-- Turns a nested array of json into a record set that we can join into
-- The value of the column in question is:
-- {"client_discounts": [{"code": 4846, "description": "Discount - AM"}, {"code": 4840, "description": "Discount - BD"}, {"code": 4836, "description": "Discount - CE"}, {"code": 4846, "description": "Discount - Cutlery"}, {"code": 4832, "description": "Discount - DE"}, {"code": 4835, "description": "Discount - Delivery Fees"}, {"code": 4831, "description": "Discount - FE"}, {"code": 4837, "description": "Discount - LL"}, {"code": 4842, "description": "Discount - Marketing Promos"}, {"code": 4852, "description": "Discount - Missing Items"}, {"code": 4833, "description": "Discount - RE"}, {"code": 4847, "description": "Delivery Service Fee"}, {"code": 4838, "description": "Discount - SOW"}, {"code": 4841, "description": "Discount - Sponsorship"}, {"code": 4844, "description": "Discount - KA"}, {"code": 4843, "description": "Discount - TE"}, {"code": 4834, "description": "Discount - VFH Delivery"}, {"code": 4849, "description": "Discount - PG"}, {"code": 4830, "description": "Discount - Sales"}, {"code": 4848, "description": "Discount - Contracts"}, {"code": 4850, "description": "Discount - Client Development"}], "restaurant_discounts": [{"code": 5120, "description": "Discount - RE"}]}
-- Becomes this:
--     code,description
--     4845,Discount - AM
--     4840,Discount - BD
--     4836,Discount - CE
--     4846,Discount - Cutlery
--     4832,Discount - DE
--     4835,Discount - Delivery Fees
--     4831,Discount - FE
--     4837,Discount - LL
--     4842,Discount - Marketing Promos
--     4852,Discount - Missing Items
--     4833,Discount - RE
--     4847,Delivery Service Fee
--     4838,Discount - SOW
--     4841,Discount - Sponsorship
--     4844,Discount - KA
--     4843,Discount - TE
--     4834,Discount - VFH Delivery
--     4849,Discount - PG
--     4830,Discount - Sales
--     4848,Discount - Contracts
--     4850,Discount - Client Development


select codes.code, codes.description
from app_configurations
         join lateral jsonb_to_recordset(
        app_configurations.preferences -> 'client_discounts') as codes(code int, description text) on true;