Select data from Json array MS SQL Server

Question

I have to select data from Json like this:

[
{
    "id": 10100,
    "externalId": "100000035",
    "name": "Test1",
    "companyId": 10099,
    "phone": "0738003811",
    "email": "[email protected]",
    "mainAddress": {
      "county": "UK",
      "province": "test",
      "zipCode": "01234",
      "city": "test",
      "street": "test",
      "gln": "44,37489331;26,21941193",
      "country": {
        "iso2": "UK",
        "iso3": "UK"
      }
    },
    "active": false,
    "main": true,
    "stores": [
      "Test"
    ],
    "attributes": [
      {
        "attributeId": 1059,
        "attributeName": "CH6 name",
        "attributeExternalId": null,
        "attributeValueId": 74292,
        "attributeValueType": "MONO_LINGUAL",
        "attributeValueEid": null,
        "attributePlainValue": "Unknown"
      },
      {
        "attributeId": 1061,
        "attributeName": "BD",
        "attributeExternalId": null,
        "attributeValueId": 81720,
        "attributeValueType": "MONO_LINGUAL",
        "attributeValueEid": null,
        "attributePlainValue": "Not assigned"
      }
 
    ],
    "daysSinceLastOrder": null
  },

   {
    "id": 62606,
    "externalId": "VL_LC_000190",
    "name": "Test",
    "companyId": 17793,
    "phone": "44333424",
    "email": "[email protected]",
    "mainAddress": {
      "firmName": "test",
      "county": "test",
      "province": "test",
      "zipCode": "247555",
      "city": "test",
      "street": "test",
      "gln": "44.8773851;23.9223518",
      "country": {
        "iso2": "RO",
        "iso3": "ROU"
      },
      "phone": "07547063789"
    },
    "active": true,
    "main": false,
    "stores": [
      "Valcea"
    ],
    "attributes": [
      {
        "attributeId": 1042,
        "attributeName": "Type of location",
        "attributeExternalId": "TYPE_OF_DIVISION",
        "attributeValueId": 34506,
        "attributeValueType": "MONO_LINGUAL",
        "attributeValueEid": "Small OTC (<40mp)",
        "attributePlainValue": "Small OTC (<40mp)"
      },
      {
        "attributeId": 17,
        "attributeName": "Limit for payment",
        "attributeExternalId": "LIMIT_FOR_PAYMENT_IN_DAYS",
        "attributeValueId": 59120,
        "attributeValueType": "NUMBER",
        "attributeValueEid": null,
        "attributePlainValue": "28"
      } 
    ],
    "daysSinceLastOrder": 147
  }
  ]

I know how to select data from simple json object using "FROM OPENJSON", but now I have to select a AttributeValueId, AttributeId and AttributeName, attributePlainValue and CompanyId for each Attribute. So I dont know how to select data from attributes array and then how to join to this CompanyId which is one level up. Maybe someone knows how write this query.

@lptr Why do you do that? Once you've made the fiddle why don't you just create an answer from it, even if it is a code-only answer — Charlieface
– Charlieface, Commented Dec 21, 2021 at 21:31

Charlieface · Accepted Answer · 2021-12-21 21:34:55Z

2

As mentioned by @lptr in the comments:

You need to pass the result of one OPENJSON to another, using CROSS APPLY. You can select a whole JSON object or array as a property, by using the syntax AS JSON

select
  t1.companyid,
  t2.*
from openjson(@j)
with (
  companyId int,
  attributes nvarchar(max) as json
) as t1
cross apply openjson(t1.attributes)
with
(
  attributeId int, 
  attributeName nvarchar(100),
  attributeValueId nvarchar(100),
  attributePlainValue nvarchar(100)
) as t2;

db<>fiddle

answered Dec 21, 2021 at 21:34

community wiki

Charlieface

Sign up to request clarification or add additional context in comments.

Comments

Wang YinXing · Accepted Answer · 2021-12-21 17:07:28Z

0

For example, you can use code like this.

f1.metaData->"$.identity.customerID" = '.$customerID.'

answered Dec 21, 2021 at 17:07

Wang YinXing

2781 silver badge6 bronze badges

Collectives™ on Stack Overflow

Select data from Json array MS SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related