英文:
How to Fetch first 10 elements from JSONB Column in postgres
问题
我的要求是从jsonb列中获取前n个元素并在UI中显示，我正在使用Java和Angular，作为第一步，我正在尝试查找是否可以在数据库中找到一些内容，以便我不需要在Java中编写任何json解析逻辑，所以我的问题是如何从postgres中检索JSONB列中的前n个元素，带有限制。
我在数据库中有这样的JSON值：
{ "firstName": "Hello", "lastName": "Hi", "address" : [{"addressLine1": "line1", "street1": "street"},
{"addresLine2": "line2","street2": "street2"} ]}
现在我想获取前n个元素，这里的元素是：firstName、LastName、AddressLine1、street1、addressLine2、street2等等.. 以1-10、11-20的限制，我在Google中搜索了像jsonb_each之类的postgres函数... 没有找到提供限制的任何内容，有办法可以做到吗？
这是我尝试的查询：
select jsonb_each(jsonbcolumn) from my_table
它将所有元素都返回为键值对。
英文:
My requirement is to fetch first n elements from jsonb column and display in UI , I'm using Java and angular, as a first step I'm trying to find whether I can found something on DB so that I don't need to write any json parsing logic in java, so my question is to retrieve first n elements from JSONB column from postgres, with limit
I have Json value like this in my DB:
{"firstName": "Hello", "lastName": "Hi", "address" : [{"addressLine1": "line1", "street1": "street"},
{"addresLine2": "line2","street2": "street2"} ]}
now I want to get the first n elements, here the elements are : firstName, LastName, AddressLine1, street1, addressLine2, street2 etc.. with limit from 1-10 , 11-20 elements etc.. I googled postgres functions like jsonb_each... and couldn't find anything which is providing limit, is there a way we can do this ?
Here is the query I tried :
select jsonb_each(jsonbcolumn) from my_table
which gave a all the elements of json into key value pair
答案1
得分: 1
你可以在一个LATERAL连接中使用jsonb_array_elements()来展开数组。选项with ordinality可以用来对数组元素进行排序并限制结果的数量：
select t.*, x.*
from mytable t
cross join lateral (
select *
from jsonb_array_elements(t.jsonbcolumn -> 'address') with ordinality as x(obj, rn)
order by x.rn
limit 10
) x
英文:
You can unnest the array with jsonb_array_elements() in a lateral join. Option with ordinality can be used to rank the array elements and limit the number of results:
select t.*, x.*
from mytable t
cross join lateral (
select *
from jsonb_array_elements(t.jsonbcolumn -> 'address') with ordinality as x(obj, rn)
order by x.rn
limit 10
) x
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论