Returning JSON arrary with particular property using Postgres-CodePudding

I have a JSONB data stored in a column called data in table nftdata. Inside this JSON data (as shown below) there is a JSON array at result.data.items. This is an array of 2400 JSON objects but I have shown two here as an example:

{
    "api_version": "0.1",
    "result": {
        "api_code": 200,
        "api_response": "Success",
        "data": {
            "collection": "XXX NFT",
            "ranking_url": "https://nft",
            "twitter": "https://twitter.com/nft/",
            "discord": "https://discord.com/invite/",
            "website": "https://nft.io/",
            "description": "2",
            "logo": "https://icons/f245c3aea5e279691f5f460d9d499afe.jpg",
            "items": [
                {
                    "id": 2414,
                    "link": "/2414",
                    "mint": "FHAJS21yJBtYDuix1x7woZPUo",
                    "name": "NFT #2414",
                    "description": "-",
                    "image": "https://arweave.net/mKbawCJP4dX9_IZg",
                    "attributes": ["Deleted to shorten post"],
                    "rank": 1,
                    "rank_algo": "h",
                    "all_ranks": {
                        "is": 1,
                        "trait_normalized": 1,
                        "statistical_rarity": 1876
                    }
                },
                {
                    "id": 198,
                    "link": "/198",
                    "mint": "EiGbBm7CsB8ZeZF5Hg",
                    "name": "NFT #198",
                    "description": "-",
                    "image": "https://arweave.net/fAVzxoqcsracjf",
                    "attributes": ["Deleted to shorten post"],
                    "rank": 2,
                    "rank_algo": "h",
                    "all_ranks": {
                        "is": 2,
                        "trait_normalized": 2,
                        "statistical_rarity": 2246
                    }
                }
            ]
        }
    }
}

The table this data is in is held like this:

collection_ID (text)	data (jsonb)
collection1	JSONB data blob (as above)
collection2	JSONB data blob (as above)

I would like to use a Postgres query to first find the right table row based on its primary key (e.g. collection1), then search through the JSON in the data column of that particular table row for one of the 2400 objects (stored at result.data.items) with a particular ID (in nodejs it would be something like result.data.items[i].id) and if found, return the particular 'items' object (e.g. result.data.items[i]).

For example, I would like to search collection1 for id 2414 and return:

{
 "id": 2414,
 "link": "/2414",
 "mint": "FHAJS21yJBtYDuix1x7woZPUo",
 "name": "NFT #2414",
 "description": "-",
 "image": "https://arweave.net/mKbawCJP4dX9_IZg",
 "attributes": [ 'Deleted to shorten post'
],
 "rank": 1,
 "rank_algo": "h",
 "all_ranks": {
     "is": 1,
     "trait_normalized": 1,
     "statistical_rarity": 1876
     }
}

CodePudding user response：

Using a SQL/JSON path expression with the function jsonb_path_query_first() this gets remarkably simple:

SELECT jsonb_path_query_first(data, '$.result.data.items[*] ? (@.id == 2414)')
FROM   nftdata
WHERE  collection_id = 'collection1';

Produces the desired result.

db<>fiddle here

Though, understanding SQL/JSON path functionality may not be as simple. It was added to Postgres 12. The manual:

To refer to the JSON data to be queried (the context item), use the $ sign in the path expression. It can be followed by one or more accessor operators, which go down the JSON structure level by level to retrieve the content of context item. Each operator that follows deals with the result of the previous evaluation step.

So:

$.result.data.items[*] ... retrieve nested array as per your definition

When defining a path, you can also use one or more filter expressions that work similarly to the WHERE clause in SQL. A filter expression begins with a question mark and provides a condition in parentheses:

? (condition)

? (@.id == 2414)') ... filter the array element that has the top level key "id" with value 2414.

CodePudding user response：

You can use built-in JSON operator and functions for that. You gave a picture instead of text, thus I am giving a simple sample based on that picture but with only two columns:

select *
from jsonb_to_recordset('{
  "result": {
    "data": {
      "items": [
        {
          "id": 1234,
          "description": "d1"
        },
        {
          "id": 1235,
          "description": "d2"
        }
      ]
    }
  }
}'::jsonb #> '{result, data, items}') as x(id int, description text);

DBFiddle demo

EDIT: As per new data in your question, you can use jsonb_array_elements (create GIN index if you haven't already did):

select item
from nftdata nd,
     jsonb_array_elements(nd.data #> '{result, data, items}') item
where (item->>'id')::int = 2414;

DBFiddle demo