Sample Header Ad - 728x90

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