From: | Gavin Henry <gavin(dot)henry(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: JSON query help |
Date: | 2019-08-28 20:22:48 |
Message-ID: | CAA8_NKD+YS6YO9xUhtmMwL7NZpgKFJTt__yiM=7Aoa5yeN5tjg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2019-08-28 20:43:16 | Re: JSON query help |
Previous Message | Gavin Henry | 2019-08-28 20:14:42 | Re: JSON query help |