filter based on json value of key/value pair
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
The function json.filter(value,'r2') returns a JSON value, not a number. You probably first need to convert the returned json value to a number, either via: json."integer"( json.filter(value,'r2')) which returns a bigint or json.number( json.filter(value,'r2')) which returns a float See also: https://www.monetdb.org/Documentation/Manuals/SQLreference/Types/JSON On 21-11-2016 18:33, Lynn Carol Johnson wrote:
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
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Thank you – that worked!
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 (4.557ms)
sql>
sql>select * from testjson where json."integer"(json.filter(value,'df')) > 1;
+------+-----------+--------------------------------------------------------------+
| chr | pos | value |
+======+===========+==============================================================+
| 2 | 234533212 | {"df":2,"pval":4.556021744872574E-6,"r2":0.0995066009518681} |
+------+-----------+--------------------------------------------------------------+
1 tuple (2.934ms)
sql>
sql>select * from testjson where json."integer"(json.filter(value,'df')) = 1;
+------+-----------+----------------------------------------------------------------+
| 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} |
+------+-----------+----------------------------------------------------------------+
3 tuples (1.464ms)
sql>
From: users-list
participants (2)
-
dinther -
Lynn Carol Johnson