We’re rewarding the question askers & reputations are being recalculated! Read more.

New answers tagged

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