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

Question

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

What exactly do you mean by "use the -> and ->> for each node in the path"? — Bergi
– Bergi, Commented Oct 29, 2022 at 20:25
when using -> and ->>, you have to put in the complete path, as far as I know and the attributes that I need to extract are nested deep in higher nodes or node arrays. I am not aware of any wildcard way of doing it - that is what I meant — adbdkb
– adbdkb, Commented Oct 30, 2022 at 13:17

Bergi · Accepted Answer · 2022-10-31 18:00:55Z

1

When you try to directly cast the jsonb value to another datatype, postgres will attempt to first convert it to a json text and then parse that. See

When you have strings in your JSON values, to avoid the quotes you'll need to extract them by using one of the json functions/operators returning text. In your case:

SELECT
    PolicyNumber AS "POLICYNUMBER",
    jsonb_path_query(payload, '$.**.ProdModelID') #>> '{}'         AS "PRODMODELID",
    (jsonb_path_query(payload, '$.**.CashOnHand') #>> '{}')::float AS "CASHONHAND"
FROM policy_json_table

answered Oct 31, 2022 at 18:00

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

adbdkb Over a year ago

Thanks. Also is there an equivalent of ** when using -> to access the nodes?

Bergi Over a year ago

No, that's only available for jsonpath. You could maybe do jsonb_path_query(payload, '$.** ? exists(@.ProdModelID)') ->> 'ProdModelID' but I don't know if this will help anything

Ramin Faracov · Accepted Answer · 2022-10-29 19:27:17Z

-1

jsonb_path_query function returns data with quotes (""), so you cannot cast this to integer or float. For casting value to integer, you need value without quotes.

You can use this SQL for getting without quotes:

Select 
    PolicyNumber AS "POLICYNUMBER",
    (payload->>'ProdModelID')::text AS "PRODMODELID",
    (payload->>'CashOnHand')::float AS "CASHONHAND"
from policy_json_table

If you need to use exactly jsonb_path_query then you can trim these quotes:

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

answered Oct 29, 2022 at 19:27

Ramin Faracov

3,3331 gold badge6 silver badges10 bronze badges

1 Comment

adbdkb Over a year ago

In the first query, the ->> doesn't return the values - I thought that was used as a wildcard for path similar to ** in the second answer. In the second query the PRODMODELID still has quotes around the value. I thought the ::text was supposed to convert it to a string, not true?

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related