How can I aggregate the results of a jsonb_path_query in postgresql to an array?

Question

I have a table with two columns, containing an ID and a JSON object (jsonb). Each json object contains several values (e.g. urls) somewhere nested inside the object. I can use jsonb_path_query to extract all those values using [*], but they are returned as one value per line. How can I aggregate the return values in a way that the resulting table has the same number of lines as the original?

Here is the example:

CREATE TABLE IF NOT EXISTS test (
  oid integer,
  object jsonb
  );

INSERT INTO test
VALUES 
    (1, '{"links": [
            {"title": "a", "url": "w"},
            {"title": "b", "url": "x"}
        ]}'),
    (2, '{"links": [
            {"title": "c", "url": "y"},
            {"title": "d", "url": "z"}
        ]}');

SELECT 
  oid,
  jsonb_path_query(object, '$.links[*].url')
FROM test;

The select query returns the following table:

| oid | jsonb_path_query |
| --- | ---------------- |
|  1  |       w          |
|  1  |       x          |
|  2  |       y          |
|  2  |       z          |

However, I would like to get this:

| oid | jsonb_path_query |
| --- | ---------------- |
|  1  |      [w,x]       |
|  2  |      [y,z]       |

user330315 · Accepted Answer · 2021-02-13 13:39:51Z

8

Use jsonb_path_query_array() - it returns all matches as a (JSON) array

SELECT oid,
       jsonb_path_query_array(object, '$.links[*].url')
FROM test;

oid | jsonb_path_query_array
----+-----------------------
  1 | ["w", "x"]            
  2 | ["y", "z"]

answered Feb 13, 2021 at 13:39

user330315

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

1 Comment

twi Over a year ago

The only excuse I have for not spotting this myself is that it's just too simple to believe. Thanks!

Collectives™ on Stack Overflow

How can I aggregate the results of a jsonb_path_query in postgresql to an array?

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