CODEWITHSUNDEEP
sqlpostgresqljsonbpostgresql-9.1jsonb-array-elements
ChlnooL
ChlnooL

Reputation: 55

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

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

Upvotes: 2

Views: 139

Answers (1)

SelVazi
SelVazi

Reputation: 16063

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

Upvotes: 2

Related Questions