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.

CodePudding user response：

For example, you can use code like this.

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

CodePudding user response：

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