Hello all -

I’m having trouble pulling rows based on the value of a json key/value pair.  I have created a table with a json object that looks like this:

sql>\d testjson

CREATE TABLE "sys"."testjson" (

"chr"   INTEGER,

"pos"   INTEGER,

"value" JSON

);

sql>

I have entries in the table that look as below:

sql>select * from testjson;

+------+-----------+----------------------------------------------------------------+

| chr  | pos       | value                                                          |

+======+===========+================================================================+

|    1 | 209890809 | {"df":1,"pval":4.556021744872574E-6,"r2":0.0995066009518681}   |

|    1 | 789383847 | {"df":1,"pval":2.50962115178055E-6,"r2":0.10462833261259474}   |

|    2 | 127893782 | {"df":1,"pval":4.2825829011938765E-6,"r2":0.10003907080878045} |

|    2 | 234533212 | {"df":2,"pval":4.556021744872574E-6,"r2":0.0995066009518681}   |

+------+-----------+----------------------------------------------------------------+

4 tuples (1.027ms)

sql>

I would like to filter on json values.  Not the json key, the json key’s value.  I can pull json values based on a non-json field, e.g. Grab the pval where chr>1 as below:

sql>select json.filter(value,'r2') as r2Value from testjson where chr=1;

+-----------------------+

| r2value               |

+=======================+

| [0.0995066009518681]  |

| [0.10462833261259474] |

+-----------------------+

2 tuples (1.179ms)

sql>

But I want to pull chr/pos where pval < some amount, or df > some number.    For example, how would I write the query to select all rows where df > 1?  This query should return  the last row of the table above.  I’ve tried the following:

sql>select * from testjson where (json.filter(value,'df') > 1);

types json(0,0) and tinyint(8,0) are not equal

sql>

Thanks for your help - Lynn