How to query nested JSONB format data column in PostgreSQL?

Question

I have data gene expression data in jsosnb column in multiple rows for different samples as shown below:

Sample       Gexp
Sample A    {"data": [{"pval": 0.0154, "Protein": "A0A0B4J2D5", "FoldChange": 1.3534, "MinusLog10p": 0.1334, "Significance": "Non-significant"}, {"pval": 0.0689, "Protein": "A0FGR8", "FoldChange": 2.5448, "MinusLog10p": 1.1615, "Significance": "Significant"}]}
Sample B    {"data": [{"pval": 0.0824, "Protein": "A0A0B4J2D5", "FoldChange": -0.1676, "MinusLog10p": 0.1084, "Significance": "Non-significant"}, {"pval": 0.0219, "Protein": "A0FGR8", "FoldChange": 2.3448, "MinusLog10p": 1.1615, "Significance": "Significant"}]}

I need to query across the column containing multiple records where a certain protein has a pval or FoldChange in a certain range. I tried multiple solutions provided in this forum (Search in nested Postgresql JSONB column, Postgresql query for objects in nested JSONB field, Query simplified JSONB form JSONB column containing nested JSON from a Postgresql database?, How to query nested array with heterogeneous elements in PostgreSQL JSONB column, etc., with no luck. Can someone help me?

klin · Accepted Answer · 2022-12-26 00:43:55Z

1

The conditions for selecting the data were not precisely (unambiguously) described in the question. Exemplary, when we are looking for the A0FGR8 protein in the pval range of 0.02 to 0.03, the query might look like this:

select sample, value
from my_table
cross join jsonb_array_elements(gexp->'data')
where value->>'Protein' = 'A0FGR8'
and (value->>'pval')::numeric between 0.02 and 0.03

Test the query in Db<>fiddle.

answered Dec 26, 2022 at 0:43

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to query nested JSONB format data column in PostgreSQL?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related