(SQL Server) JSON FindRecord Example

Demonstrates the FindRecord method for searching an array of JSON records. The data used in this example is available at JSON sample data for FindRecord.

Note: This example requires Chilkat v9.5.0.63 or later.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- Note: This example requires Chilkat v9.5.0.63 or later.
    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @json, 'LoadFile', @success OUT, 'qa_data/json/qb_accounts.json'

    -- A sample of the content of qb_accounts.json is shown at the bottom of this example.
    -- The goal is to search the array of Account records to return the 1st match

    -- Find the account with the name "Advertising"
    DECLARE @arrayPath nvarchar(4000)
    SELECT @arrayPath = 'QueryResponse.Account'
    DECLARE @relativePath nvarchar(4000)
    SELECT @relativePath = 'Name'
    DECLARE @value nvarchar(4000)
    SELECT @value = 'Advertising'
    DECLARE @caseSensitive int
    SELECT @caseSensitive = 1

    DECLARE @accountRec int
    EXEC sp_OAMethod @json, 'FindRecord', @accountRec OUT, @arrayPath, @relativePath, @value, @caseSensitive
    EXEC sp_OAGetProperty @json, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN

        PRINT 'Record not found.'
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    -- The accountRec should contain this:

    --       {
    --         "Name": "Advertising",
    --         "SubAccount": false,
    --         "FullyQualifiedName": "Advertising",
    --         "Active": true,
    --         "Classification": "Expense",
    --         "AccountType": "Expense",
    --         "AccountSubType": "AdvertisingPromotional",
    --         "CurrentBalance": 0,
    --         "CurrentBalanceWithSubAccounts": 0,
    --         "CurrencyRef": {
    --           "value": "USD",
    --           "name": "United States Dollar"
    --         },
    --         "domain": "QBO",
    --         "sparse": false,
    --         "Id": "7",
    --         "SyncToken": "0",
    --         "MetaData": {
    --           "CreateTime": "2016-09-09T14:42:07-07:00",
    --           "LastUpdatedTime": "2016-09-09T14:42:07-07:00"
    --         }
    --       }


    EXEC sp_OAMethod @accountRec, 'StringOf', @sTmp0 OUT, 'FullyQualifiedName'
    PRINT 'FullyQualifiedName: ' + @sTmp0

    EXEC sp_OAMethod @accountRec, 'StringOf', @sTmp0 OUT, 'AccountType'
    PRINT 'AccountType: ' + @sTmp0

    EXEC sp_OAMethod @accountRec, 'StringOf', @sTmp0 OUT, 'AccountSubType'
    PRINT 'AccountSubType: ' + @sTmp0

    PRINT '----'
    EXEC @hr = sp_OADestroy @accountRec

    -- ------------------------------------------------------------------
    -- Find the first account where the currency is USD
    SELECT @relativePath = 'CurrencyRef.value'
    SELECT @value = 'USD'
    SELECT @caseSensitive = 1

    EXEC sp_OAMethod @json, 'FindRecord', @accountRec OUT, @arrayPath, @relativePath, @value, @caseSensitive
    EXEC sp_OAGetProperty @json, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN

        PRINT 'Record not found.'
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    EXEC sp_OAMethod @accountRec, 'StringOf', @sTmp0 OUT, 'Name'
    PRINT 'Name: ' + @sTmp0

    EXEC sp_OAMethod @accountRec, 'StringOf', @sTmp0 OUT, 'CurrencyRef.name'
    PRINT 'CurrencyRef.name: ' + @sTmp0

    PRINT '----'
    EXEC @hr = sp_OADestroy @accountRec

    -- ------------------------------------------------------------------
    -- Find the first account with "receivable" in the name (case insensitive)
    SELECT @relativePath = 'Name'
    SELECT @value = '*receivable*'
    SELECT @caseSensitive = 0

    EXEC sp_OAMethod @json, 'FindRecord', @accountRec OUT, @arrayPath, @relativePath, @value, @caseSensitive
    EXEC sp_OAGetProperty @json, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN

        PRINT 'Record not found.'
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    EXEC sp_OAMethod @accountRec, 'StringOf', @sTmp0 OUT, 'Name'
    PRINT 'Name: ' + @sTmp0

    PRINT '----'
    EXEC @hr = sp_OADestroy @accountRec

    -- -----------------------------------------------------------------
    -- qb_accounts.json contains this data
    -- 
    -- {
    --   "QueryResponse": {
    --     "Account": [
    --       {
    --         "Name": "Accounts Payable (A/P)",
    --         "SubAccount": false,
    --         "Description": "Description added during update.",
    --         "FullyQualifiedName": "Accounts Payable (A/P)",
    --         "Active": true,
    --         "Classification": "Liability",
    --         "AccountType": "Accounts Payable",
    --         "AccountSubType": "AccountsPayable",
    --         "CurrentBalance": -1602.67,
    --         "CurrentBalanceWithSubAccounts": -1602.67,
    --         "CurrencyRef": {
    --           "value": "USD",
    --           "name": "United States Dollar"
    --         },
    --         "domain": "QBO",
    --         "sparse": false,
    --         "Id": "33",
    --         "SyncToken": "1",
    --         "MetaData": {
    --           "CreateTime": "2016-09-10T10:12:02-07:00",
    --           "LastUpdatedTime": "2016-10-24T16:41:39-07:00"
    --         }
    --       },
    --       {
    --         "Name": "Accounts Receivable (A/R)",
    --         "SubAccount": false,
    --         "FullyQualifiedName": "Accounts Receivable (A/R)",
    --         "Active": true,
    --         "Classification": "Asset",
    --         "AccountType": "Accounts Receivable",
    --         "AccountSubType": "AccountsReceivable",
    --         "CurrentBalance": 5281.52,
    --         "CurrentBalanceWithSubAccounts": 5281.52,
    --         "CurrencyRef": {
    --           "value": "USD",
    --           "name": "United States Dollar"
    --         },
    --         "domain": "QBO",
    --         "sparse": false,
    --         "Id": "84",
    --         "SyncToken": "0",
    --         "MetaData": {
    --           "CreateTime": "2016-09-14T14:49:29-07:00",
    --           "LastUpdatedTime": "2016-09-17T13:16:17-07:00"
    --         }
    --       },
    --       {
    --         "Name": "Advertising",
    --         "SubAccount": false,
    --         "FullyQualifiedName": "Advertising",
    --         "Active": true,
    --         "Classification": "Expense",
    --         "AccountType": "Expense",
    --         "AccountSubType": "AdvertisingPromotional",
    --         "CurrentBalance": 0,
    --         "CurrentBalanceWithSubAccounts": 0,
    --         "CurrencyRef": {
    --           "value": "USD",
    --           "name": "United States Dollar"
    --         },
    --         "domain": "QBO",
    --         "sparse": false,
    --         "Id": "7",
    --         "SyncToken": "0",
    --         "MetaData": {
    --           "CreateTime": "2016-09-09T14:42:07-07:00",
    --           "LastUpdatedTime": "2016-09-09T14:42:07-07:00"
    --         }
    --       },
    --       {
    --         "Name": "Arizona Dept. of Revenue Payable",
    --         "SubAccount": false,
    --         "FullyQualifiedName": "Arizona Dept. of Revenue Payable",
    --         "Active": true,
    --         "Classification": "Liability",
    --         "AccountType": "Other Current Liability",
    --         "AccountSubType": "GlobalTaxPayable",
    --         "CurrentBalance": 0,
    --         "CurrentBalanceWithSubAccounts": 0,
    --         "CurrencyRef": {
    --           "value": "USD",
    --           "name": "United States Dollar"
    --         },
    --         "domain": "QBO",
    --         "sparse": false,
    --         "Id": "89",
    --         "SyncToken": "0",
    --         "MetaData": {
    --           "CreateTime": "2016-09-16T12:17:04-07:00",
    --           "LastUpdatedTime": "2016-09-17T13:05:01-07:00"
    --         }
    --       },
    --       {
    --         "Name": "Automobile",
    --         "SubAccount": false,
    --         "FullyQualifiedName": "Automobile",
    --         "Active": true,
    --         "Classification": "Expense",
    --         "AccountType": "Expense",
    --         "AccountSubType": "Auto",
    --         "CurrentBalance": 0,
    --         "CurrentBalanceWithSubAccounts": 0,
    --         "CurrencyRef": {
    --           "value": "USD",
    --           "name": "United States Dollar"
    --         },
    --         "domain": "QBO",
    --         "sparse": false,
    --         "Id": "55",
    --         "SyncToken": "0",
    --         "MetaData": {
    --           "CreateTime": "2016-09-14T10:15:53-07:00",
    --           "LastUpdatedTime": "2016-09-14T10:16:05-07:00"
    --         }
    --       },
    -- ...
    -- 

    EXEC @hr = sp_OADestroy @json


END
GO