PostgreSQL中利用其他列从JSONB字段获取数据的SQL查询需求

Got it, let's tackle this problem. You need to dynamically pull the value from the scores JSONB column that corresponds to the value in the domain_name column—PostgreSQL's JSONB operators make this straightforward.

Solution Query #

Here's the SQL that does exactly what you need:

SELECT
  id,
  domain_name,
  -- Extract the text value matching the domain_name
  scores->'medica'->'categories'->>domain_name AS domain_score
FROM survey_results;

How It Works #

Let's break down the JSONB operations step by step:

• scores->'medica': Navigates to the medica object inside the scores JSONB column (returns a JSONB object).
• ->'categories': Drills down further into the categories nested object (still JSONB).
• ->>domain_name: Uses the value from the domain_name column as the dynamic key to extract the corresponding text value. The ->> operator returns a text string instead of a JSONB type, which is usually what you want for direct use in reports or further processing.

If You Need JSONB Instead of Text #

If you want to keep the result as a JSONB value (instead of plain text), swap ->> with ->:

SELECT
  id,
  domain_name,
  scores->'medica'->'categories'->domain_name AS domain_score_json
FROM survey_results;

Expected Output #

For your sample data, the first query will return:

内容的提问来源于stack exchange，提问作者Mateusz Urbański