Querying into JSON in PostgreSQL without using a subquery

Question

I'm interested in creating a query of a raw json blob without having to use a subquery in PostgreSQL. The query looks like this:

 SELECT id, 
    ((db_bench_results.result::json -> 'Runner'::text)) as runner
   FROM public.db_bench_results as full_results 
   WHERE ((db_bench_results.result::json -> 'Runner'::text)) = "this_runner";

However, when I do this, I get:

ERROR:  column "this_runner" does not exist
LINE 11: ...E ((db_bench_results.result::json -> 'Runner'::text)) = "this_runner";

Is this possible without creating a subquery and joining the tables together?

text in postgres uses single quotes ', double quotes " are reserved for named fields, so just change the condition to = 'this_runner' — Optimum
– Optimum, Commented Jan 19, 2022 at 20:46

user330315 · Accepted Answer · 2022-01-19 21:14:27Z

3

String constants have to be enclosed in single quotes in SQL. The double quotes are for identifiers.

As you want to compare a value as text, use the ->> operator to make the returned value a text not a jsonb (or json) value.

So the following should work:

SELECT id, 
       db_bench_results.result ->> 'Runner' as runner
FROM public.db_bench_results as full_results 
WHERE db_bench_results.result ->> 'Runner' = 'this_runner';

This assumes that results is a jsonb (which it should be) or json column.

answered Jan 19, 2022 at 21:14

user330315

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

Comments

aronchick · Accepted Answer · 2022-01-19 20:28:12Z

0

Ended up doing this, is this efficient?

 SELECT f.runner from (select ((db_bench_results.result -> 'Runner'::text))::text AS runner,
   FROM public.db_bench_results) as f where f.runner like '%this_runner%'

answered Jan 19, 2022 at 20:28

aronchick

7,15812 gold badges58 silver badges93 bronze badges

Collectives™ on Stack Overflow

Querying into JSON in PostgreSQL without using a subquery

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related