jsonb_each访问对象内部的元素

Question

我有一个JSON列(名为"roi")，其中包含用户的Instagram性能。这是roi列:

{
"data": {
    "campaignName": "Master Cosy",
    "currency": "GBP",
    "reportData": {
        "AAAAAAAAAA": {
            "id": "0f20d833-d0f-bdb7-19",
            "name": "cornish_gregorys",
            "thumbnail": "https://sstagram.com/v/t51.2885-19/s320x320/87244862_1017848048596",
            "Name": "cornisorys",
            "instagramCount": 2319,
            "instagramEngagementFactor": 0,
            "instagramAuthorised": true,
            "hasPosts": true,
            "budget": 0,
            "derivedFee": 0,
            "inventoryItems": [],
            "trackedAssetsStats": {
                "totalAssets": 9,
                "facebook": {
                    "count": 0
                },
                "instagram": {
                    "total": 9,
                    "stories": 9,
                    "carousels": 0,
                    "videos": 0,
                    "images": 0,
                    "igtvs": 0
                },
        "BBBBBBBBBBBBB": {
            "id": "d3d30db4-0b453dfc3ae2a09",
            "name": "itssdha",
            "thumbnail": "https://in9809609728_n.jpg?_nc_ht=instagram.fhel5-1.fna.fbcdn.net&_nc_ohc=Se3ySAoqnFwAX4f6&oeF1623",
            "Name": "itsshdha",
            "instagramCount": 26700,
            "instagramEngagementFactor": 0,
            "instagramAuthorised": true,
            "hasPosts": true,
            "budget": 0,
            "derivedFee": 0,
            "inventoryItems": [],
            "trackedAssetsStats": {
                "totalAssets": 5,
                "facebook": {
                    "count": 0
                },
            "instagram": {
                "total": 9,
                "stories": 9,
                "carousels": 0,
                "videos": 0,
                "images": 0,
                "igtvs": 0}, etc.....

后"reportData"我有用户的具体名称(在本例中为AAAAAAAA和bbbbbbb)以及他们的Instagram帐户的表现。如何访问对象用户名内的所有指标，而无需键入特定的用户名(AAAAAAAA和bbbbbbb)

我的查询是:

roi -> 'date' -> 'reportData' -> 'AAAAAAA' -> 'instagramCount' -> etc ....

但我需要一些东西来"跳过"这一部分-> 'AAAAAAA' ->并直接进入指标，在这种情况下是'instagramCount'，等等…

从我所读到的，我可能需要使用jsonb_each有人知道怎么用吗?

Answer 1

demos:db<>fiddle

你有几种方法。

1. 使用jsonb_each()扩展所有用户的数据:您可以为每个用户创建一条记录，然后再请求count

   SELECT
       users.value -> 'instagramCount'
   FROM
       mytable,
       jsonb_each(mydata -> 'data' -> 'reportData') as users
   

2. Since Postgres 12您可以使用JSONpath来实现相同的效果:

   SELECT 
       jsonb_path_query(mydata, '$.**.instagramCount') 
   FROM mytable