Question 300236 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres select json array and remap property name

I need to remap the names of properties in the objects of an array in postgres when performing a SELECT query.

The table user_phones has columns id and phones. Imagine phones column type is json and the array has the following structure:

[
  {
    model: "samsung s23 utra",
    serial_number: 123456
  },
  {
    model: "apple i-phone 15",
    serial_number: 123477
  }
]

When I perform the select query I want the phones property in the response to be returned as:

[
  {
    model: "samsung s23 utra",
    serialNumber: 123456
  },
  {
    model: "apple i-phone 15",
    serialNumber: 123477
  }
]

I was thinking somewhere along the lines of SELECT p.id, json_build_array(SELECT <this is where I'm stuck>) as p.phones FROM user_phones as p;

I’ve added fiddle here

Is there a simple way of performing this in postgres?

Tags:

2

Answers


  1. 0

    Use the json_array_elements() function to expand a JSON array into multiple rows, then apply json_build_object to reconstruct the JSON elements, and finally, use json_agg() to consolidate these elements back into a JSON array.

    SELECT id, json_agg(
              json_build_object('model', value->'model', 'serialNumber', value->'serial_number')::json
           ) new_json
FROM user_phones u
CROSS JOIN json_array_elements(u.phones) 
GROUP BY id

    Results :

    id  new_json
1   [{"model" : "samsung s23 utra", "serialNumber" : 123456}, {"model" : "apple i-phone 15", "serialNumber" : 123477}]

    Demo here

    Login or Signup to reply.
  2. 0

    Cast to text, replace(), cast back to json: Demo at db<>fiddle

    select id, replace(phones::text,'"serial_number"','"serialNumber"')::json
from user_phones;
    id jsonb_pretty
    1 [
        {
            "model": "samsung s23 utra",
            "serialNumber": "12345"
        },
        {
            "model": "iphone15",
            "serialNumber": "12333"
        }
    ]

    It’ll target anything matching the pattern regardless of whether that’s actually a key or a value, or a part of either. On the upside, it’s way simpler, faster and frees you from worrying about the current and future structure of objects in your array.

    If you deconstruct, rename, reconstruct, you’ll lose keys and values of all elements which structure you didn’t explicitly address, whenever an element has a missing or renamed key, or has some additional data in it.

    Another way that’s free from the downsides of blind text replacement is to use jsonb operator - to remove the old key and || to re-add it with a new name:

    select id, json_agg(   e - 'serial_number'
                    || jsonb_build_object('serialNumber',e->'serial_number') )
from user_phones,jsonb_array_elements(phones::jsonb)_(e) 
group by 1;

    That way you really only rename the key in each object, without any assumptions about the structure around it. Be warned that switching from json to jsonb will compact insignificant whitespace, as well as order and deduplicate your keys.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search