PostgreSQL - jsonb - How to get the datatype for value in query with jsonpath
0
votes
1
answer
1876
views
In PostgreSQL using jsonb column, is there a way to select / convert an attribute with actual datatype the datatype instead of getting it as a string object when using jsonpath? I would like to try to avoid cast as well as -> and ->> type of construct since I have to select many attributes with very deep paths, I am trying to do it using jsonpath and * or ** in the path
Is it possible to do it this way or must I use the -> and ->> for each node in the path ? This will make the query look complicated as I have to select about 35+ attributes in the select with quite deep paths.
Also, how do we remove quotes from the selected value?
This is what I was trying, but doesn't work to remove quotes from Text value and gives an error on numeric
Select
PolicyNumber AS "POLICYNUMBER",
jsonb_path_query(payload, '$.**.ProdModelID')::text AS "PRODMODELID",
jsonb_path_query(payload, '$.**.CashOnHand')::float AS "CASHONHAND"
from policy_json_table
the PRODMODELID still shows the quotes around the value and when I add ::float to second column, it gives an error
SQL Error : ERROR: cannot cast jsonb string to type double precision
Thank you
Asked by adbdkb
(103 rep)
Oct 27, 2022, 10:51 AM
Last activity: Oct 28, 2022, 04:03 AM
Last activity: Oct 28, 2022, 04:03 AM