Postgresql JSon rows to key-value

Question

currently I have following postgresql json object in form of arrays object

[
  {
    "id": 1,
    "name": "dollar",
    "sign": "$",
    "exchange_rate": 1
  },
  {
    "id": 2,
    "name": "Riel",
    "sign": "៛",
    "exchange_rate": 4085
  },
  {
    "id": 3,
    "name": "Baht",
    "sign": "b",
    "exchange_rate": 30.55
  },
  {
    "id": 4,
    "name": "Dong",
    "sign": "d",
    "exchange_rate": 16000
  }
]

and the arrays object is the result of the following query:

select json_agg(row_to_json(record))
from (
select
    c.id,
    c.name,
    c.sign,
    c.exchange_rate
from  currency c) record;

but the result above is slightly different to my needs. I want to switch that arrays to another form of key, value object just as below, so I can traversal the json through id value.

{
      "1": {
        "name": "dollar",
        "sign": "$",
        "exchange_rate": 1
      },
      "2": {
        "name": "Riel",
        "sign": "៛",
        "exchange_rate": 4085
      },
      "3": {
        "name": "Baht",
        "sign": "b",
        "exchange_rate": 30.55
      },
      "4": {
        "name": "Dong",
        "sign": "d",
        "exchange_rate": 16000
      }
    }

Anyone provide me some help for this problem pls. Respect all your answers.

Barbaros Özhan · Accepted Answer · 2020-12-16 22:57:14Z

2

JSON_BUILD_OBJECT() along with JSON_OBJECT_AGG() might be used

SELECT JSON_OBJECT_AGG(id,
                       JSON_BUILD_OBJECT('name',name,
                                         'sign',sign,
                                         'exchange_rate',exchange_rate))
  FROM currency

or keeping the original function consider briefly using

SELECT JSON_OBJECT_AGG(id, ROW_TO_JSON(c)::JSONB - 'id' )
  FROM currency AS c

Demo

P.S: JSONB_PRETTY() might be added, as stated in the demo, wrapping up the applied outermost function, in order to display the result formatted well.

edited Dec 16, 2020 at 22:57

answered Dec 16, 2020 at 20:48

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

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

1 Comment

Chhunly Lim Over a year ago

Thanks for your help. I can make it now.

Collectives™ on Stack Overflow

Postgresql JSon rows to key-value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related