New answers tagged json
1
$ oerr ora 40573
40573, 00000, "Invalid use of PL/SQL JSON object type."
// *Cause: An attempt was made to use a PL/SQL JavaScript Object Notation
// (JSON) object type in an invalid context.
// *Action: Use PL/SQL JSON object types only inside PL/SQL.
Example:
set serveroutput on
declare
l_objects tmp_011040_t2;
l_index pls_integer;
begin
...
0
If someone needs Row to Whole table json encoded linked by column (id). This is the way to go:
SELECT main_table.*, subVirt.subrows_json
FROM main_table
LEFT JOIN (
SELECT parent_id, json_agg(row_to_json(sub_table)) AS subrows_json
FROM sub_table
GROUP BY 1
) subVirt ON subVirt.parent_id = main_table.id
So the result is each row from ...
1
Your case is too dynamic for json_populate_record(): it takes a record type which you don't know (nor have) at the time of calling.
PROCEDURE calling UPDATE for each JSON key
Proof of concept to show what did not work in your attempt.
CREATE PROCEDURE record_event (
foo_arg int,
name_arg text,
data_arg jsonb,
occurred_at_arg timestamptz
) AS
$...
1
This seems to do what you want:
select jsonb_build_object('uid', b.uid, 'nested', jsonb_agg(to_jsonb(m)))
from base b
join lateral (
select n.uid, n.message
from nested n
join jsonb_array_elements_text(b.nested) as x(bid) on x.bid = n.uid
) m on true
group by b.uid
order by b.uid;
Online example: https://rextester.com/ASA37564
0
Remove the GIN index delivery_expr_idx you created and make sure that created still has an index.
Then try
SELECT *
FROM (SELECT *
FROM delivery
ORDER BY created
OFFSET 0 LIMIT 10000) AS q
WHERE document @>
'{"links": [{"type": "receipt","externalId": "9990863500764120180212"}]}'
ORDER BY created
LIMIT 100;
I'm cheating with the ...
0
This should do it:
SELECT jsonb_agg(student) AS students
FROM (
SELECT jsonb_object_agg((kv).key, (kv).value) AS student
FROM (
SELECT ass->>'student_id' AS student_id, "char" 'a' AS tbl, id, jsonb_each(ass) AS kv
FROM (SELECT jsonb_array_elements(assessment ) AS ass, id FROM assessment) a
UNION ALL
SELECT ass->&...
Top 50 recent answers are included
