Re: JSON query help - Mailing list pgsql-novice

Hi Tom,

This is 9.6 with BDR in. Is my way to tackle this correct? To restate,
there is an event in the top level json with a list of things with a
BalanceUUID in. Then a list of your balances. I want to pull out
records that have used one of those balances, but there could be one
or more in there, hence the jsonb_object_keys

Here's some other errors trying this.

HINT:  No operator matches the given name and argument type(s). You
might need to add explicit type casts.
SELECT cost_details->'Accounting'->jsonb_object_keys(cost_details->'Accounting')->'BalanceUUID'
AS BalanceUUID,
cost_details->'Accounting'->jsonb_object_keys(cost_details->'Accounting')->'Units'
AS Cost from cdrs WHERE cost > 0 AND
cost_details->'AccountSummary'->'BalanceSummaries' @>
'[{"ID":"B_MONETARY_POSTPAID"}]' AND
cost_details->'AccountSummary'->'BalanceSummaries' @>
jsonb_build_object('UUID',
cost_details->'Accounting'->jsonb_object_keys(cost_details->'Accounting')->'BalanceUUID')
limit 1;
ERROR:  function jsonb_build_object(unknown, jsonb) does not exist
LINE 1: ..._details->'AccountSummary'->'BalanceSummaries' @> jsonb_buil...
                                                             ^
HINT:  No function matches the given name and argument types. You
might need to add explicit type casts.
SELECT cost_details->'Accounting'->jsonb_object_keys(cost_details->'Accounting')->'BalanceUUID'
AS BalanceUUID,
cost_details->'Accounting'->jsonb_object_keys(cost_details->'Accounting')->'Units'
AS Cost from cdrs WHERE cost > 0 AND
cost_details->'AccountSummary'->'BalanceSummaries' @>
'[{"ID":"B_MONETARY_POSTPAID"}]' AND
cost_details->'AccountSummary'->'BalanceSummaries' @>
json_build_object('UUID',
cost_details->'Accounting'->jsonb_object_keys(cost_details->'Accounting')->'BalanceUUID')
limit 1;
ERROR:  operator does not exist: jsonb @> json
LINE 1: ...ost_details->'AccountSummary'->'BalanceSummaries' @> json_bu...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You
might need to add explicit type casts.
SELECT cost_details->'Accounting'->jsonb_object_keys(cost_details->'Accounting')->'BalanceUUID'
AS BalanceUUID,
cost_details->'Accounting'->jsonb_object_keys(cost_details->'Accounting')->'Units'
AS Cost from cdrs WHERE cost > 0 AND
cost_details->'AccountSummary'->'BalanceSummaries' @>
'[{"ID":"B_MONETARY_POSTPAID"}]' AND
cost_details->'AccountSummary'->'BalanceSummaries' @>
jsonb_build_object('UUID',
cost_details->'Accounting'->jsonb_object_keys(cost_details->'Accounting')->'BalanceUUID')
limit 1;
ERROR:  function jsonb_build_object(unknown, jsonb) does not exist
LINE 1: ..._details->'AccountSummary'->'BalanceSummaries' @> jsonb_buil...


How can I check if that function does exist?

Thanks.