jsonb_each_text() and json_each_text()

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

Signature For the jsonb variant:

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

Notes: The result of jsonb_each_text() bears the same relationship to the result of jsonb_each() as does the result of the ->> operator to that of the -> operator. For that reason, jsonb_each_text() is useful when the results are primitive values.

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

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

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'::text),
      ('b', 'dog'::text),
      ('c', 'true'::text),
      ('d', '{"a": 17, "b": "cat"}'::text)
    ];

  k_v_pair t;
  n int := 0;
begin
  for k_v_pair in (
    select key, value from jsonb_each_text(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$;

Notice that even here, 17 and true are SQL text values and not, respectively, a SQL numeric value and a SQL boolean value. Notice too that the ::text typecast of the underlying jsonb value dog is "dog" (as is produced by jsonb_each()) while when it is read as a SQL text value by jsonb_each_text the result is dog.