Postgres 9.6中处理含JSON数组的jsonb字段的复杂查询问题

Got it, let's walk through how to tackle complex queries on your myjsontable with the JSONB array column. First, let's confirm your table structure (I'll finish that incomplete index too, since proper indexing is key for JSONB performance):

CREATE TABLE myjsontable(data JSONB NOT NULL);
INSERT INTO myjsontable VALUES ('[{"score":20 ,"category": 10 }, {"score":100 ,"category": 100 },{"score":500 ,"category": 50 }]');
INSERT INTO myjsontable VALUES ('[{"score":1000 ,"category": 40 }, {"score":30 ,"category": 50 },{"score":6000 ,"category": 100 }]');
INSERT INTO myjsontable VALUES ('[{"score":10 ,"category": 1 }, {"score":123 ,"category": 40 },{"score":1000 ,"category": 50 }]');
-- Create a GIN index for efficient general JSONB queries
CREATE INDEX idx_myjsontable_data_gin ON myjsontable USING GIN (data);

Now let's cover some common complex query scenarios you might need, with practical SQL examples:

1. Unnest JSON Array into Individual Rows #

If you want to work with array elements as separate rows (instead of dealing with the whole array), use jsonb_array_elements to expand the data:

SELECT 
  elem->>'category' AS category,
  (elem->>'score')::INT AS score
FROM myjsontable,
     jsonb_array_elements(data) AS elem;

This gives you a flat list of every score-category pair across all rows, making it easy to filter or aggregate.

2. Filter Rows with Specific Array Element Conditions #

Suppose you need to find all rows where the array has at least one element with score > 500 and category = 100:

SELECT *
FROM myjsontable
WHERE EXISTS (
  SELECT 1
  FROM jsonb_array_elements(data) AS elem
  WHERE (elem->>'score')::INT > 500
    AND (elem->>'category')::INT = 100
);

For exact matches, you can also use the @> operator (faster with GIN indexes):

SELECT *
FROM myjsontable
WHERE data @> '[{"score": 6000, "category": 100}]'::JSONB;

3. Aggregate Data Across All Array Elements #

If you want to calculate totals, averages, or counts grouped by category across every element in the table:

SELECT 
  (elem->>'category')::INT AS category,
  SUM((elem->>'score')::INT) AS total_score,
  AVG((elem->>'score')::INT) AS avg_score,
  COUNT(*) AS element_count
FROM myjsontable,
     jsonb_array_elements(data) AS elem
GROUP BY (elem->>'category')::INT
ORDER BY total_score DESC;

4. Find Rows with Multiple Matching Array Elements #

Let's say you need rows where the array contains both a category = 40 element AND a category = 50 element:

SELECT *
FROM myjsontable
WHERE data @> '[{"category": 40}]'::JSONB
  AND data @> '[{"category": 50}]'::JSONB;

The @> operator checks for the presence of each required element, and works well with our GIN index.

5. Optimize for Targeted Queries #

If you frequently query on specific keys like score or category, create functional indexes to speed things up even more:

-- Index for category value lookups
CREATE INDEX idx_myjsontable_data_category ON myjsontable USING GIN (jsonb_path_query_array(data, '$.category'));

-- Index for numeric score range queries
CREATE INDEX idx_myjsontable_data_score ON myjsontable USING GIN (jsonb_path_query_array(data, '$.score'));

内容的提问来源于stack exchange，提问作者Yuval Kaufman