How to get PostgreSQL's JSON type data in laravel?

Question

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;

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?

Laurenz Albe · Accepted Answer · 2016-10-05 06:38:14Z

1

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'"
         );

edited Oct 5, 2016 at 6:38

answered Oct 4, 2016 at 13:23

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

How to get PostgreSQL's JSON type data in laravel?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related