Chandrashekar Bemagoni Chandrashekar Bemagoni - 1 month ago 7
JSON Question

How to get postgress JSON type data in laravell?

I created table

-- Table: public.books

-- DROP TABLE public.books;

CREATE TABLE public.books
(
id integer,
data json
)
WITH (
OIDS=FALSE
);
ALTER TABLE public.books
OWNER TO postgres;

-- Index: public.books_author_first_name

-- DROP INDEX public.books_author_first_name;

CREATE UNIQUE INDEX books_author_first_name
ON public.books
USING btree
(((data -> 'author'::text) ->> 'first_name'::text) COLLATE pg_catalog."default");


and

select * from books;


enter image description here

from the laravel:

$testPJSON=DB::select('SELECT * FROM books WHERE data->>"last_name" = "White"');


getting null;
what is the best way to get data in Laravel from Postgres JSON type?

Answer

Your query should match the data. There is no element last_name in the JSON object.

Try this SQL statement:

$testP2=DB::select(
          "SELECT * FROM books WHERE data->'author'->>'last_name' = 'White'"
         );
Comments