0

All I have is a PostgreSQL table that has a JSON column called agents.
See below:

SELECT agents FROM cnms_rosters;

Result:

      agents      
------------------
 [1,13,3,16,15]
 [12,13,14,15,15]
 [11,73,55,16,44]
(3 rows)

This column contains the id of an agent. What I want to do is to query the row or rows that has the specific agent id.

How can I achieve that?

I am currently using Laravel framework. Is there any Eloquent approach in querying the data? If not, how to query it using DB::raw or other query builder?

KazikM
  • 139
  • 2
  • 9
Yves Gonzaga
  • 109
  • 3

1 Answers1

1
Select id, a from (SELECT Id,
  json_array_elements(r.agents) A
   FROM Cnms_rosters r) ra 
   Where A::TEXT = '13';

See http://sqlfiddle.com/#!17/eb400/19

Gerard H. Pille
  • 3,255
  • 1
  • 9
  • 13