CODEWITHSUNDEEP
postgresqlpostgresql-10
MojoJojo
MojoJojo

Reputation: 4232

PostgreSQL: Filter and Aggregate on JSONB Array type

Consider the following table definition:

CREATE TABLE keys
(
    id bigint NOT NULL DEFAULT nextval('id_seq'::regclass),
    key_value jsonb[] NOT NULL DEFAULT ARRAY[]::jsonb[],
)

The table now contains the following values:

id | key_value
---|-----------
1  | {"{\"a\": \1\", \"b\": \"2\", \"c\": \"3\"}","{\"a\": \"4\", \"b\": \"5\", \"c\": \"6\"}","{\"a\": \"7\", \"b\": \"8\", \"c\": \"9\"}"} |

How do I:

  1. Select all rows where value of b is NOT 2? I tried using the @> operator,
  2. For the returned rows, for each key_value object, return c - a

My confusion stems from the fact that all methods dealing with JSONB in postgres seem to accept JSON or JSONB but none seem to work with JSONB[]. Not sure what I am missing?

Thanks in advance

Upvotes: 0

Views: 273

Answers (1)

Mike Organek
Mike Organek

Reputation: 12484

What could be better than doing this with unnest and normal relational operations?

array types and json are abominations in the face of the perfection that is relational sets. The first rule of holes is that when you find yourself in one, stop digging and climb out of the hole.

with unwind as (
  select id, unnest(key_value) as kvjson
    from keys
)
select id, (kvjson->>'c')::int - (kvjson->>'a')::int as difference
  from unwind
 where kvjson->>'b' != '2';

Upvotes: 1

Related Questions