Question 115057 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Laravel Json Column search in array of objects where key value greater / less than

I have a laravel model that has a JSON column type named "properties".
This column is a array of objects like below

[
   {
      "quantity":4210,
      "price":"21247.10",
      "childs":[
         {
            "quantity":0.19814469,
            "price":"22329.70"
         }
      ]
   },
   {
      "quantity":1234,
      "price":"21247.10",
      "childs":[
         {
            "quantity":0.19814469,
            "price":"22329.70"
         }
      ]
   }
]

I need to perform searches where "price" greater than or less than another value?

How to achieve that? using postgres and json columns

Tags:

2

Answers


  1. 0

    One of PostgreSQL’s benefits is that it’s a relational database, but you can also get the advantages of unstructured data by storing things in a JSON column. Here’s how you can query your JSON column in PostgreSQL:

    -- Give me params.name (text) from the events table
select params->>'name' from events;

-- Find only events with a specific name
select * from events where params->>'name' = 'Click Button';

-- Give me the first index of a JSON array
select params->ids->0 from events;

-- Find users where preferences.beta is true (boolean)
-- This requires type casting preferences->'beta' from json to boolean
select preferences->'beta' from users where (preferences->>'beta')::boolean is true;

    The short arrow -> keeps the type as JSON, and the long arrow ->> returns text.

    Edit
    In your case you should do something like: 

    $users = DB::table('--your-table')
                ->where('properties->price', 'your comparison operator here', '--Your value here--')
                ->get();
    Login or Signup to reply.
  2. 0

    This solution works fine

    $users = DB::table('tableName')
    ->where('properties->price', 'like', '%' . $request->search . '%')
    ->get();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search