TSQL query to dynamically extract data from a json object using OPENJSON in SQL

Question

TSQL query to dynamically extract data from a json object using OPENJSON in SQL

tobz 161

Hi everyone.

I need help with writing a TSQL query that will dynamically extract data from a JSON object using OPENJSON. I need the query to be able to work on both simple and nested json object.

Thanks in advance.

Yitzhak Khabinsky 26,586 Reputation points

2021-11-25T05:25:20.843+00:00

@tobz ,

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)

2 answers

Your answer

Yitzhak Khabinsky 26,586 Reputation points

2021-11-25T05:25:20.843+00:00

@tobz ,

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)

Answer 1

Hi @tobz ，

For this type of problem we recommend that you post CREATE TABLE statements
for your tables together with INSERT statements with sample data,
your JSON Schema ,enough to illustrate all angles of the problem.
We also need to see the expected result of the sample.

The following example should help you:

DECLARE @json NVARCHAR(MAX) = N'[  
  {  
    "ID": "1",  
    "Measure": "Current Sales",  
    "2019Week12": "33",  
    "2019Week13": "33",  
    "2019Week14": "34"  
  },  
  {  
    "ID": "2",  
    "Measure": "Current Sales",  
    "2019Week12": "",  
    "2019Week13": "10",  
    "2019Week14": "60"  
  }]';  
  
  
SELECT   JSON_VALUE(A.[value],'$.ID') AS ID  
        ,JSON_VALUE(A.[value],'$.Measure') AS Measure  
        ,B.[key] AS [varName]  
        ,B.[value] AS [varValue]    
        ,ROW_NUMBER() OVER(PARTITION BY JSON_VALUE(A.[value],'$.ID') ORDER BY B.[key]) RowIndex  
FROM OPENJSON(@json) A  
CROSS APPLY OPENJSON(A.[value]) B  
WHERE b.[key] NOT IN('ID','Measure');

We use OPENJSON() to dive into json string. This will return the two objects contained in a derived set A.
Now we use OPENJSON() again passing in A.[value], which is the json object itself.
This would return all contained items, but we surpress ID and Measurement within WHERE.
The two sepcial columns, ID and Measurement we fetch directly from A.[value] using JSON_VALUE().

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards,
Echo

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Thanks.

So this is the json object I am passing.

However, I want the query to be a dynamic SQL

{
  "id": 158831,
  "token_id": "1",
  "num_sales": 3,
  "background_color": null,
  "image_url": "https://lh3.googleusercontent.com/7bRocEaoBrWYBX3vThkHj4kAV3b3mKG-Kem85xeT-D8oHpvQ19kcoiBd9mIFeNU0GrwZGvj6Oc5NAEGBSsGlrww",
  "image_preview_url": "https://lh3.googleusercontent.com/7bRocEaoBrWYBX3vThkHj4kAV3b3mKG-Kem85xeT-D8oHpvQ19kcoiBd9mIFeNU0GrwZGvj6Oc5NAEGBSsGlrww=s250",
  "image_thumbnail_url": "https://lh3.googleusercontent.com/7bRocEaoBrWYBX3vThkHj4kAV3b3mKG-Kem85xeT-D8oHpvQ19kcoiBd9mIFeNU0GrwZGvj6Oc5NAEGBSsGlrww=s128",
  "image_original_url": "https://www.larvalabs.com/cryptopunks/cryptopunk1.png",
  "animation_url": null,
  "animation_original_url": null,
  "name": "CryptoPunk #1",
  "description": null,
  "external_link": "https://www.larvalabs.com/cryptopunks/details/1",
  "asset_contract": {
    "address": "0xb47e3cd837ddf8e4c57f05d70ab865de6e193bbb",
    "asset_contract_type": "non-fungible",
    "created_date": "2018-01-23T04:51:38.832339",
    "name": "CryptoPunks",
    "nft_version": "unsupported",
    "opensea_version": null,
    "owner": null,
    "schema_name": "CRYPTOPUNKS",
    "symbol": "PUNK",
    "total_supply": null,
    "description": "CryptoPunks launched as a fixed set of 10,000 items in mid-2017 and became one of the inspirations for the ERC-721 standard. They have been featured in places like The New York Times, Christie’s of London, Art|Basel Miami, and The PBS NewsHour.",
    "external_link": "https://www.larvalabs.com/cryptopunks",
    "image_url": "https://lh3.googleusercontent.com/BdxvLseXcfl57BiuQcQYdJ64v-aI8din7WPk0Pgo3qQFhAUH-B6i-dCqqc_mCkRIzULmwzwecnohLhrcH8A9mpWIZqA7ygc52Sr81hE=s120",
    "default_to_fiat": false,
    "dev_buyer_fee_basis_points": 0,
    "dev_seller_fee_basis_points": 0,
    "only_proxied_transfers": false,
    "opensea_buyer_fee_basis_points": 0,
    "opensea_seller_fee_basis_points": 250,
    "buyer_fee_basis_points": 0,
    "seller_fee_basis_points": 250,
    "payout_address": null
  },
  "permalink": "https://opensea.io/assets/0xb47e3cd837ddf8e4c57f05d70ab865de6e193bbb/1",
  "collection": {
    "payment_tokens": [
      {
        "id": 4645681,
        "symbol": "WETH",
        "address": "0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2",
        "image_url": "https://storage.opensea.io/files/accae6b6fb3888cbff27a013729c22dc.svg",
        "name": "Wrapped Ether",
        "decimals": 18,
        "eth_price": 1,
        "usd_price": 4033.09
      },
      {
        "id": 13689077,
        "symbol": "ETH",
        "address": "0x0000000000000000000000000000000000000000",
        "image_url": "https://storage.opensea.io/files/6f8e2979d428180222796ff4a33ab929.svg",
        "name": "Ether",
        "decimals": 18,
        "eth_price": 1,
        "usd_price": 4033.09
      },
      {
        "id": 12182941,
        "symbol": "DAI",
        "address": "0x6b175474e89094c44da98b954eedeac495271d0f",
        "image_url": "https://storage.opensea.io/files/8ef8fb3fe707f693e57cdbfea130c24c.svg",
        "name": "Dai Stablecoin",
        "decimals": 18,
        "eth_price": 0.0002479,
        "usd_price": 1
      },
      {
        "id": 4403908,
        "symbol": "USDC",
        "address": "0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48",
        "image_url": "https://storage.opensea.io/files/749015f009a66abcb3bbb3502ae2f1ce.svg",
        "name": "USD Coin",
        "decimals": 6,
        "eth_price": 0.00024781,
        "usd_price": 1
      }
    ],
    "primary_asset_contracts": [
      {
        "address": "0xb47e3cd837ddf8e4c57f05d70ab865de6e193bbb",
        "asset_contract_type": "non-fungible",
        "created_date": "2018-01-23T04:51:38.832339",
        "name": "CryptoPunks",
        "nft_version": "unsupported",
        "opensea_version": null,
        "owner": null,
        "schema_name": "CRYPTOPUNKS",
        "symbol": "PUNK",
        "total_supply": null,
        "description": "CryptoPunks launched as a fixed set of 10,000 items in mid-2017 and became one of the inspirations for the ERC-721 standard. They have been featured in places like The New York Times, Christie’s of London, Art|Basel Miami, and The PBS NewsHour.",
        "external_link": "https://www.larvalabs.com/cryptopunks",
        "image_url": "https://lh3.googleusercontent.com/BdxvLseXcfl57BiuQcQYdJ64v-aI8din7WPk0Pgo3qQFhAUH-B6i-dCqqc_mCkRIzULmwzwecnohLhrcH8A9mpWIZqA7ygc52Sr81hE=s120",
        "default_to_fiat": false,
        "dev_buyer_fee_basis_points": 0,
        "dev_seller_fee_basis_points": 0,
        "only_proxied_transfers": false,
        "opensea_buyer_fee_basis_points": 0,
        "opensea_seller_fee_basis_points": 250,
        "buyer_fee_basis_points": 0,
        "seller_fee_basis_points": 250,
        "payout_address": null
      }
    ],
    "traits": {},
    "stats": {
      "one_day_volume": 396.95,
      "one_day_change": 0.05790987178222963,
      "one_day_sales": 4,
      "one_day_average_price": 99.2375,
      "seven_day_volume": 5712.410999999999,
      "seven_day_change": -0.3673949443963332,
      "seven_day_sales": 53,
      "seven_day_average_price": 107.7813396226415,
      "thirty_day_volume": 33065.29522300001,
      "thirty_day_change": -0.8117331567161005,
      "thirty_day_sales": 302,
      "thirty_day_average_price": 108.78657358609284,
      "total_volume": 726668.7812493035,
      "total_sales": 17858,
      "total_supply": 9999,
      "count": 9999,
      "num_owners": 3233,
      "average_price": 40.69149855803019,
      "num_reports": 4,
      "market_cap": 1077705.6148867924,
      "floor_price": 0
    },
    "banner_image_url": "https://lh3.googleusercontent.com/48oVuDyfe_xhs24BC2TTVcaYCX7rrU5mpuQLyTgRDbKHj2PtzKZsQ5qC3xTH4ar34wwAXxEKH8uUDPAGffbg7boeGYqX6op5vBDcbA=s2500",
    "chat_url": null,
    "created_date": "2019-04-26T22:13:09.691572",
    "default_to_fiat": false,
    "description": "CryptoPunks launched as a fixed set of 10,000 items in mid-2017 and became one of the inspirations for the ERC-721 standard. They have been featured in places like The New York Times, Christie’s of London, Art|Basel Miami, and The PBS NewsHour.",
    "dev_buyer_fee_basis_points": "0",
    "dev_seller_fee_basis_points": "0",
    "discord_url": "https://discord.gg/tQp4pSE",
    "display_data": {
      "card_display_style": "cover"
    },
    "external_url": "https://www.larvalabs.com/cryptopunks",
    "featured": false,
    "featured_image_url": "https://storage.opensea.io/0xb47e3cd837ddf8e4c57f05d70ab865de6e193bbb-featured-1556589448.png",
    "hidden": false,
    "safelist_request_status": "verified",
    "image_url": "https://lh3.googleusercontent.com/BdxvLseXcfl57BiuQcQYdJ64v-aI8din7WPk0Pgo3qQFhAUH-B6i-dCqqc_mCkRIzULmwzwecnohLhrcH8A9mpWIZqA7ygc52Sr81hE=s120",
    "is_subject_to_whitelist": false,
    "large_image_url": "https://lh3.googleusercontent.com/QB2kKuQEw04X02V9EoC2BNYZV652LYuewUv9ZdR7KJfI9Jocwmd28jIfsGg0umSCr2bOMV8O9UpLAkoaqfYwvwmC",
    "medium_username": null,
    "name": "CryptoPunks",
    "only_proxied_transfers": false,
    "opensea_buyer_fee_basis_points": "0",
    "opensea_seller_fee_basis_points": "250",
    "payout_address": null,
    "require_email": false,
    "short_description": null,
    "slug": "cryptopunks",
    "telegram_url": null,
    "twitter_username": "larvalabs",
    "instagram_username": null,
    "wiki_url": null
  },
  "decimals": null,
  "token_metadata": "",
  "owner": {
    "user": null,
    "profile_img_url": "https://storage.googleapis.com/opensea-static/opensea-profile/32.png",
    "address": "0xb88f61e6fbda83fbfffabe364112137480398018",
    "config": ""
  },
  "sell_orders": null,
  "creator": {
    "user": null,
    "profile_img_url": "https://storage.googleapis.com/opensea-static/opensea-profile/22.png",
    "address": "0xc352b534e8b987e036a93539fd6897f53488e56a",
    "config": ""
  },
  "traits": [
    {
      "trait_type": "type",
      "value": "Male",
      "display_type": null,
      "max_value": null,
      "trait_count": 6039,
      "order": null
    },
    {
      "trait_type": "accessory",
      "value": "Mohawk",
      "display_type": null,
      "max_value": null,
      "trait_count": 441,
      "order": null
    },
    {
      "trait_type": "accessory",
      "value": "Smile",
      "display_type": null,
      "max_value": null,
      "trait_count": 238,
      "order": null
    },
    {
      "trait_type": "accessory",
      "value": "2 attributes",
      "display_type": null,
      "max_value": null,
      "trait_count": 230,
      "order": null
    }
  ],
  "last_sale": {
    "asset": {
      "token_id": "1",
      "decimals": null
    },
    "asset_bundle": null,
    "event_type": "successful",
    "event_timestamp": "2020-11-30T18:44:26",
    "auction_type": null,
    "total_price": "60000000000000000000",
    "payment_token": {
      "id": 1,
      "symbol": "ETH",
      "address": "0x0000000000000000000000000000000000000000",
      "image_url": "https://storage.opensea.io/files/6f8e2979d428180222796ff4a33ab929.svg",
      "name": "Ether",
      "decimals": 18,
      "eth_price": "1.000000000000000",
      "usd_price": "4038.730000000000018000"
    },
    "transaction": {
      "block_hash": "0xe3eacbc6f4d6bb43525b69baffe09477f452f0f5e1a5b1d5232dc23b6cb176cb",
      "block_number": "11361817",
      "from_account": {
        "user": {
          "username": "GoWestBTC"
        },
        "profile_img_url": "https://storage.googleapis.com/opensea-static/opensea-profile/4.png",
        "address": "0xee3766e4f996dc0e0f8c929954eaafef3441de87",
        "config": ""
      },
      "id": 63641091,
      "timestamp": "2020-11-30T18:44:26",
      "to_account": {
        "user": null,
        "profile_img_url": "https://storage.googleapis.com/opensea-static/opensea-profile/23.png",
        "address": "0xb47e3cd837ddf8e4c57f05d70ab865de6e193bbb",
        "config": ""
      },
      "transaction_hash": "0xf4af5563f3c4c3b26dee3ab027902f113bee5985b28d9ede5b81ab42b46abb30",
      "transaction_index": "58"
    },
    "created_date": "2020-11-30T18:45:11.476313",
    "quantity": "1"
  },
  "top_bid": null,
  "listing_date": null,
  "is_presale": false,
  "transfer_fee_payment_token": null,
  "transfer_fee": null,
  "related_assets": [],
  "orders": [],
  "auctions": [],
  "supports_wyvern": false,
  "top_ownerships": [
    {
      "owner": {
        "user": null,
        "profile_img_url": "https://storage.googleapis.com/opensea-static/opensea-profile/32.png",
        "address": "0xb88f61e6fbda83fbfffabe364112137480398018",
        "config": ""
      },
      "quantity": "1"
    }
  ],
  "ownership": null,
  "highest_buyer_commitment": null
}

Ronen Ariely 15,206 Reputation points

2021-11-28T09:48:47.817+00:00

Hi

TSQL query to dynamically extract data from a json object using OPENJSON in SQL

There are infinite ways to extract data from this json text into different tabular structures

(1) Please provide the expected result which you want to get from your sample text.

(2) Please explain what part of the JSON text is dynamic and elaborate on what options we can have - add at least another sample with DIFFERENT structure please so we will be able to discuss the dynamic request as well.
Ronen Ariely 15,206 Reputation points

2021-11-28T09:52:26.903+00:00

Tip: for long text, you can upload a text file with the content

Share via

TSQL query to dynamically extract data from a json object using OPENJSON in SQL

2 answers

Your answer