PostgreSQL – jsonb – How to get the datatype for value in query with jsonpath

datatypesjson-pathpostgresql

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 [22023]: ERROR: cannot cast jsonb string to type double precision

Thank you

Best Answer

Remember that jsonb_path_query() returns jsonb objects. If you want the contents of the object (even if it is a primitive type), extract element 0. There is no support for extracting values into anything other than json(b) or text, so you will have to then convert text to whatever numeric type you need.

->>0 only works for jsonb primitives, oddly enough.

Select 
    PolicyNumber AS "POLICYNUMBER",
    jsonb_path_query(payload, '$.**.ProdModelID')->>0 AS "PRODMODELID",
    (jsonb_path_query(payload, '$.**.CashOnHand')->>0)::float AS "CASHONHAND"
from policy_json_table

Aside: Please, please, please don't use floating point for currency. Your accountants will kill you over the disappearing/reappearing pennies. Use numeric for currency values.

Related Solutions

Oracle integer to number conversion

A DATA_LENGTH of 22 is just the largest number of bytes it can take to store the largest possible number (Oracle uses 2 digits per byte). A NULL DATA_PRECISION implies the maximum of 38.

You can verify this with DBMS_METADATA:

SQL>  create table sotmp ( a int );

Table created.

SQL> select dbms_metadata.get_ddl('TABLE','SOTMP')
  2  from dual;

DBMS_METADATA.GET_DDL('TABLE','SOTMP')
--------------------------------------------------------------------------------

  CREATE TABLE "PHIL"."SOTMP"
   (    "A" NUMBER(*,0)
   ) SEGMENT CREATION DEFER

SQL>

(The "*" in the above output denotes the maximum precision).

BINARY_INTEGER is not a data type that can be used in table columns. It can only be used as a PL/SQL variable datatype.

Postgresql – Create multi-key JSON object with integers or floats as values

Update

According to your sample data and desired result you can get it using json_object_agg Postgres function.

CREATE TABLE test (str text, num double precision);
INSERT INTO test(str, num) VALUES ('a', 1.0);
INSERT INTO test(str, num) VALUES ('b', 2.3);

select json_object_agg(str, num)
from test;

| json_object_agg        |
| :--------------------- |
| { "a" : 1, "b" : 2.3 } |

dbfiddle here

You can use row_to_json Postgres function.

create table foo(f1 int, f2 varchar(10), f3 float, f4 decimal(18,2));
insert into foo values (2, 'name', 2.345, 23.23);

select row_to_json(f)
from   (select f1,f2,f3,f4 from foo) f;

| row_to_json                                |
| :----------------------------------------- |
| {"f1":2,"f2":"name","f3":2.345,"f4":23.23} |

dbfiddle here

Best Answer

Related Solutions

Oracle integer to number conversion

Postgresql – Create multi-key JSON object with integers or floats as values

Related Question