So, I have a table (Using postgres v9) that has an id(integer) and a json(text). This is the query that I use to fetch it:

select software_json from software_master where id=1;

And I get

{
  "Result": [
    {
      "name": "C#",
      "value": "1"
    },
    {
      "name": "JAVA",
      "value": "2"
    },
    {
      "name": "JAVASCRIPT",
      "value": "3"
    },
    {
      "name": "PHP",
      "value": "4"
    },
    {
      "name": "PYTHON",
      "value": "5"
    },
    {
      "name": "TSQL",
      "value": "6"
    }
  ]
}

I want the output to have less records and still be a json

{
  "Result": [
    {
      "name": "C#",
      "value": "1"
    },
    {
      "name": "JAVA",
      "value": "2"
    }
  ]
}

So, if there are more than 100 values, I want to fetch only 100 records in json format. How do I do it?

CodePudding user response：

To select the 100 first jsonb objects in a jsonb array :

select jsonb_build_object('Result', jsonb_agg(j.data))
  from software_master 
 cross join lateral jsonb_array_elements(software_json :: jsonb -> 'Result') WITH ORDINALITY AS j(data, rank)
 where id = 1
   and j.rank <= 100
;

CodePudding user response：

If you just want to pick the first two entries from the array you can use something like this:

select jsonb_build_object(
         'Result', 
         jsonb_path_query_array(software_json, '$.Result[*] ? (@.value == "1" || @.value == "2")')
       )
from software_master

It would be easier if the values wasn't stored as a string, then you could use e.g. '$.Result[*] ? (@.value <= 2)'

This assumes that software_json is defined as a jsonb column (which it should be). If it's not, you need to cast it: software_json::jsonb