CODEWITHSUNDEEP
sqljsonpostgresqljsonb
Chhunly Lim
Chhunly Lim

Reputation: 170

Postgresql JSon rows to key-value

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.

Upvotes: 0

Views: 1503

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65198

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.

Upvotes: 2

Related Questions