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.

select '{"a":"foo", "b":"bar"}'::jsonb; {"a":"foo", "b":"bar"}. The jsonb_array_elements won't work as you don't have a JSON array. — Adrian Klaver
– Adrian Klaver, Commented Jan 25, 2021 at 22:17
Thanks - so basically put "select" in front of the examples in the linked doc — Adam Hughes
– Adam Hughes, Commented Jan 25, 2021 at 22:28

score 1 · Accepted Answer · 2021-01-25 22:19:21Z

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;

edited Jan 25, 2021 at 22:19

answered Jan 25, 2021 at 22:13

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Adam Hughes Over a year ago

Thanks this is great. For completeness, can you also mention how one could store the JSON variable in a declared variable so don't have to have long json string in the select statement?

user330315 Over a year ago

@AdamHughes: SQL has no variables, so you would need PL/pgSQL for that. But you can't really display a result from within a PL/pgSQL procedure.

Collectives™ on Stack Overflow

How to declare a JSON string in postgres?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related