postgresql - How to declare a JSON string in postgres?

Question

There are many examples of json parsing in POSTGRES, which pull data from a table. I have a raw json string handy and would like to practice using JSON functions and operators. Is it possible to do this without using tables? Or ... what is the most straightfoward way to declare it as a variable? Something like...

# Declare
foojson = "{'a':'foo', 'b':'bar'}"

# Use
jsonb_array_elements(foojson) -> 'a'

Basically I'd like the last line to print to console or be wrappable in a SELECT statement so I can rapidly "play" with some of these operators.

question from:https://stackoverflow.com/questions/65893087/how-to-declare-a-json-string-in-postgres

Answer 1

You can pass it directly to the function

select '{"a": "foo", "b": "bar"}'::jsonb ->> 'a';

select *
from jsonb_each('{"a": "foo", "b": "bar"}');

select *
from jsonb_array_elements('[{"a": "foo"}, {"b": "bar"}]');

Or if you want to pretend, it's part of a table:

with data (json_value) as (
  values 
    ('{"a": "foo", "b": "bar"}'::jsonb), 
    ('{"foo": 42, "x": 100}')
)
select e.*
from data d
  cross join jsonb_each(d.json_value) as e;


with data (json_value) as (
  values 
    ('{"a": 1, "b": "x"}'::jsonb), 
    ('{"a": 42, "b": "y"}')
)
select d.json_value ->> 'a',
       d.json_value ->> 'b'
from data d;