jsonb_each() and json_each()

This page documents the preview version (v2.21). Preview includes features under active development and is for development and testing only. For production, use the stable version (v2024.1). To learn more, see Versioning.

Purpose: Create a row set with columns "key" (as a SQL text) and "value" (as a SQL jsonb) from a JSON object.

Signature For the jsonb variant:

input value:       jsonb
return value:      SETOF (text, jsonb)

Use this ysqlsh script to create the required type "t" and then to execute the ASSERT.

create type t as (k text, v jsonb);

do $body$
declare
  object constant jsonb :=
    '{"a": 17, "b": "dog", "c": true, "d": {"a": 17, "b": "cat"}}';

  k_v_pairs t[] := null;
  expected_k_v_pairs constant t[] :=
    array[
      ('a', '17'::jsonb),
      ('b', '"dog"'::jsonb),
      ('c', 'true'::jsonb),
      ('d', '{"a": 17, "b": "cat"}'::jsonb)
    ];

  k_v_pair t;
  n int := 0;
begin
  for k_v_pair in (
    select key, value from jsonb_each(object)
    )
  loop
    n := n + 1;
    k_v_pairs[n] := k_v_pair;
  end loop;

  assert
    k_v_pairs = expected_k_v_pairs,
  'unexpected';
end;
$body$;