Chandrashekar Bemagoni Chandrashekar Bemagoni - 8 months ago 53
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
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");


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?


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

Try this SQL statement:

          "SELECT * FROM books WHERE data->'author'->>'last_name' = 'White'"