How to convert elements to snake_case format in jsonb column array [Postgresql]

Question

I have a database table(call it table) with jsonb column(lets say column). Column contains bellow data:

{
  "country": "GB",
  "channel": "mobile",
  "profileGroups: [
    {
       "profiles": ["profileA", "profileB"],
       "negativeProfiles: ["negativeA"
    },{
       "profiles": ["profileC"],
       "negativeProfiles": null
    }
  ]
}

Now I want to create view for this table with fields in snake_case format, so it should looks like:

{
  "country": "GB",
  "channel": "mobile",
  "profile_groups: [
    {
       "profiles": ["profileA", "profileB"],
       "negative_profiles: ["negativeA"
    },{
       "profiles": ["profileC"],
       "negative_profiles": null
    }
  ]
}

My latest query looks like this:

CREATE OR REPLACE VIEW v_table
SELECT json_build_object(
'country', column_1 -> 'country',
'channel', column_1 -> 'channel',
'profile_groups', column_1 -> 'profileGroups'
)::jsonb as column_1
FROM table;

How to transform data from inside of profileGroups array?

Example at DB Fiddle

You have invalid JSON ! Can you fix it

SelVazi
– SelVazi

2023-12-22 13:30:49 +00:00
Commented Dec 22, 2023 at 13:30 — SelVazi
– SelVazi, Commented Dec 22, 2023 at 13:30

SelVazi · Accepted Answer · 2023-12-22 14:20:19Z

2

If the elements which you want convert are already known then we can use a bunch of nested replace() statements :

CREATE OR REPLACE VIEW v_table AS
SELECT REPLACE(REPLACE(column_1::TEXT, 'profileGroups', 'profile_groups'), 'negativeProfiles', 'negative_profiles')::jsonb AS column_1
FROM mytable;

Demo here

answered Dec 22, 2023 at 14:20

SelVazi

16.2k2 gold badges20 silver badges35 bronze badges

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

Collectives™ on Stack Overflow

How to convert elements to snake_case format in jsonb column array [Postgresql]

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related