0

So, my goal is the get the count of all times when a value appears in a column.

I have a table (my_table) with a jsob type column result. The value in the column looks something like this:

[
 {
    "id": "b28a6f8c-b504-4623-a033-ea74d258d76f",
    "code": "S0255"

},
{
    "id": "773c571b-db7a-4219-a003-36a89dcc5cb9",
    "code": "T02816"
},
{
    "id": "ddfdb739-46f3-4640-a119-34a283ce81df",
    "code": "S0255"

}
]

The result should be : count = 2

This is an array of objects, hence the confusion(Is there an alternate query I can use to test?

My current query is this :

 SELECT COUNT(DISTINCT id) as codeCount
         FROM my_table
         CROSS JOIN jsonb_array_elements(result) as res
         WHERE res ->> 'code' = 'S0255'
         AND result != JSONB '[]'

I am getting the following error :

org.jkiss.dbeaver.model.sql.DBSQLException: SQL Error [22023]: ERROR: cannot extract elements from an object

How do I resolve this? Also is there a different way to achieve this?

Edit: after some more research I research I found the column contained both array and plain object values, I just added one more filter and it runs fine now.

   SELECT COUNT(DISTINCT id) as codeCount
         FROM my_table
        CROSS JOIN jsonb_array_elements(result) as res
         WHERE res ->> 'code' = 'S0255'
         AND result != JSONB '[]'
         and  jsonb_typeof(result) = 'array'
6
  • Hi, and welcome to dba.se! What happens if you run your query on a psql or pgAgdmin client?
    – Vérace
    Jun 9, 2022 at 21:21
  • I'm currently using Dbeaver
    – tickedoff
    Jun 9, 2022 at 21:28
  • I know - that' a Java client. I want (ideally) to know what error you obtain using the native one or, failing that, pgAdmin?
    – Vérace
    Jun 9, 2022 at 21:31
  • Is there a different way to achieve this irrespective of the client?
    – tickedoff
    Jun 9, 2022 at 21:58
  • 2
    You could only have got that error if your JSON was an object, not an array. Compare this fiddle dbfiddle.uk/… and this dbfiddle.uk/… What is your real JSON? Jun 9, 2022 at 22:37

2 Answers 2

0

The only way to get this error is if there is some row in my_table that is not a JSON array. Find the offending rows with

SELECT id
FROM my_table
WHERE jsonb_typeof(result) <> 'array';
2
  • Ok, I ran this query and there are both arrays and just plain objects stored in the column.
    – tickedoff
    Jun 10, 2022 at 3:29
  • Then the mystery is solved, right? Jun 10, 2022 at 5:34
0

If I understand your question correctly, you want to know how many elements the array has with a specific code value.

In that case a JSON path query might be easier to deal with as it is more lenient about the structure:

select jsonb_path_query_array(result, '$[*] ? (@.code == "S0255").id')
from my_table;

With your sample data, this returns ["b28a6f8c-b504-4623-a033-ea74d258d76f", "ddfdb739-46f3-4640-a119-34a283ce81df"]. If the result column contains something that is not an array (or array elements that don't have a code key), then the result is an empty array.

To get the number of elements from that array you can use:

jsonb_array_length(jsonb_path_query_array(result, '$[*] ? (@.code == "S0255").id'))

The major difference to your query is that this won't apply a DISTINCT on the IDs that are returned. If you really do need the distinct, you can unnest the result array from the JSON path:

select count(distinct id)
from my_table
  cross join jsonb_array_elements_text(jsonb_path_query_array(result, '$[*] ? (@.code == "S0255").id')) as res(id)
where result @> '[{"code": "S0255"}]'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.