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

Postgresql – Query jsonb array and return search index

I’ve the following dataset

Table 1
-------
Id | JsonB
1  | [ { "Name": "ABC" }, { "Name": "DEF" } ]
2  | [ { "Name": "XYZ" }, { "Name": "ABC" } ]
3  | [ { "Name": "GHI" }, { "Name": "JKL" } ]

I want to return the index of the value in search. For example when searching for {"Name": "ABC"} it would return the below result. Note: my search is on a unique property, however just in case if there are more than one occurrence it can return an array of indexes.

Query result
--------------------
Id | Index
1  | [0]
2  | [1]
3  | null or []

EDIT: because Postgresql works with paths and in case of nested search, may it’s better to return the path instead, but I am happy with any

Query result
--------------------
Id | Path
1  | ['{0}']
2  | ['{1}']
3  | null or []
Tags:

3

Answers


  1. 0

    If you want the result as a JSON array, not a PostgreSQL array, you could use

    SELECT json.id,
       nullif(jsonb_agg(u.n - 1), '[null]')
FROM json
   LEFT JOIN LATERAL jsonb_array_elements(json.jsonb) WITH ORDINALITY AS u(e, n)
      ON u.e = '{"Name": "ABC"}'
GROUP BY json.id;

    jsonb_array_elements() unnests the JSON array and WITH ORDINALITY gibes us the position in the array.

    Login or Signup to reply.
  2. 0

    You can use jsonb_array_elements with ordinality to get the position of each element :

    SELECT id, ARRAY_AGG(ordinality- 1) as index
FROM mytable 
LEFT JOIN jsonb_array_elements(myjson) with ordinality
      ON value = '{"Name": "ABC"}'
GROUP BY id
ORDER By id

    Demo here

    Login or Signup to reply.
  3. 0

    You can use the jsonb_array_elements table function to extract the objects to match against and the WITH ORDINALITY option to generate an index. Put them in a subquery and aggregate the indices of the matched objects back into an array:

    SELECT
  id,
  (
    SELECT array_agg(ord-1)
    FROM jsonb_array_elements(array_of_objects) WITH ORDINALITY AS values(obj, ord)
    WHERE obj = '{ "Name": "ABC" }'
  ) AS index
FROM demo;

    (online demo)

    Notice I’ve used ord - 1 to make the index 0-based, ordinality (and array indexing) is 1-based in Postgres.

    If there is no match, array_agg will return NULL, to get an empty array instead, wrap the subquery in ARRAY(…). Or if you want a json array, use COALESCE((SELECT json_agg(…) FROM …), '[]').

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

Back To Top
Search