Building query in Postgres 9.4.2 for JSONB datatype using builtin function

Question

I have a table schema as follows:

DummyTable
-------------
someData JSONB

All my values will be a JSON object. For example, when you do a select * from DummyTable, it would look like

someData(JSONB)
------------------
{"values":["P1","P2","P3"],"key":"ProductOne"}
{"values":["P3"],"key":"ProductTwo"}

I want a query which will give me result set as follows:

[
  {
    "values": ["P1","P2","P3"],
    "key": "ProductOne"
  },
  {
    "values": ["P4"],
   "key": "ProductTwo"
  }
]

I'm using Postgres version 9.4.2. I looked at documentation page of the same, but could not find the query which would give the above result.

However, in my API, I can build the JSON by iterating over rows, but I would prefer query doing the same. I tried json_build_array, row_to_json on a result which would be given by select * from table_name, but no luck.

Any help would be appreciated.

Here is the link I looked for to write a query for JSONB

Tometzky · Accepted Answer · 2017-05-04 20:03:46Z

1

You can use json_agg or jsonb_agg:

create table dummytable(somedata jsonb not null);
insert into dummytable(somedata) values
  ('{"values":["P1","P2","P3"],"key":"ProductOne"}'),
  ('{"values":["P3"],"key":"ProductTwo"}');
select jsonb_pretty(jsonb_agg(somedata)) from dummytable;

Result:

[
    {
        "key": "ProductOne",
        "values": [
            "P1",
            "P2",
            "P3"
        ]
    },
    {
        "key": "ProductTwo",
        "values": [
            "P3"
        ]
    }
]

Although retrieving the data row by row and building on client side can be made more efficient, as the server can start to send data much sooner - after it retrieves first matching row from storage. If it needs to build the json array first, it would need to retrieve all the rows and merge them before being able to start sending data.

answered May 4, 2017 at 20:03

Tometzky

24.2k5 gold badges64 silver badges79 bronze badges

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

Collectives™ on Stack Overflow

Building query in Postgres 9.4.2 for JSONB datatype using builtin function

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