Re: JSON query help

Gavin Henry <[email protected]> Wed, 28 Aug 2019 20:40:04 +0100

Newsgroups	gmane.comp.db.postgresql.novice
Message-ID	<CAA8_NKBwNeYmoAVHicePw9j_H+D2uewBXUHjqwQP4_TcqAjNfg@mail.gmail.com>

I think I'm pretty close:

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'::text,
cost_details->'Accounting'->jsonb_object_keys(cost_details->'Accounting')->'BalanceUUID')::jsonb
LIMIT 1;

ERROR:  function jsonb_build_object(text, 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.