Convert JSONB field from single value to object with own keys

Question

I have a table, let's call it myTable with the following structure

ID   | data
____________
uuid | jsonb

The data in the jsonb field is an array structured in the following way:

[
  {
    "valueA": "500",
    "valueB": "ABC",
  },
  {
    "valueA": "300",
    "valueB": "CDE",
  }
]

What I want to do is transform that data by converting valueB to be an object, with newKey that corresposnds to the current value of "valueB"

This is the result I want:

[
  {
    "valueA": "500",
    "valueB": {"newKey": "ABC"},
  },
  {
    "valueA": "300",
    "valueB": {"newKey": "CDE"},
  }
]

I tried doing it with the following query:

UPDATE myTable
SET data = (
    SELECT jsonb_agg (
        jsonb_insert(elems, '{valueB, newKey}', elems->'valueB')
    )
    FROM jsonb_array_elements(data) elems
);

It doesn't seem to do anything unfortunately.

Another idea I have is to create a new field, initialize it as an object, then delete the old onde and rename the new one, but it seems there must be a way to do what I want directly?

Franjo Pintarić · Accepted Answer · 2022-04-25 08:01:26Z

1

Solved using jsonb_build_object()

UPDATE myTable
SET data = (
    SELECT jsonb_agg (
        jsonb_insert(elems, '{valueB}', jsonb_build_object('newKey', elems->'valueB'))
    )
    FROM jsonb_array_elements(data) elems
);

answered Apr 25, 2022 at 8:01

Franjo Pintarić

4114 silver badges13 bronze badges

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

Collectives™ on Stack Overflow

Convert JSONB field from single value to object with own keys

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