Querying into JSON in PostgreSQL without using a subquery-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

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%'