How to join nested JSON indices to multiple rows in SQL by primary key

Question

I am trying to update several rows in SQL with JSON.

I'd like to match a primary key on a table row to an index nested in an array of JS objects.

Sample data:

let json = [{
  "header": object_data,
  "items": [{
    "id": {
      "i": 0,
      "name": "item_id"
    },
    "meta": {
      "data": object_data,
      "text": "some_text"
    }
  }, {
    "id": {
      "i": 4,
      "name": "item_id4"
    },
    "meta": {
      "data": object_data,
      "text": "some_text"
    }
  }, {
    "id": {
      "i": 17,
      "name": "item_id17"
    },
    "meta": {
      "data": object_data,
      "text": "some_text"
  }}]
}]

Sample table:

i  |  json                     | item_id
---+---------------------------+---------
0  | entire_object_at_index_0  | item_id
4  | entire_object_at_index_4  | item_id4
17 | entire_object_at_index_17 | item_id17

entire_object_at_index, meaning appending the item data to the header to create a new object for each row.

"header" "some_data",
"items": [{
  "id": {
    "i": 0,
    "name": "item_id1"
  },
  "meta": {
    "data": "some_data",
    "text": "some_text"
  }
}]

SQL:

update someTable set 
json = json_value(@jsons, '$') -- not sure how to join on index here
item_id = json_value(@jsons, '$.items[?].id.name' -- not sure how to select by index here
where [i] = json_query(@jsons, '$.items.id.i')

What's supposed to end up in json? meta? meta.data? The object holding id and meta? Something else? In other words, what should the table look like afterwards? Also, is the index in the array truly relevant, or can we just resolve everything by matching i? — Jeroen Mostert
– Jeroen Mostert, Commented Jul 29, 2019 at 13:49
In JSON it's a new object that has the header data and item data for that key/index. — ang
– ang, Commented Jul 29, 2019 at 14:00
The index is not truly relevant, it's just matching the table key i to the object index id.i. — ang
– ang, Commented Jul 29, 2019 at 14:01
So, just to be clear, in every row of the table the header and items array should be repeated, with the items array having only a single element in each case? Are you allowed to assume the structure (i.e. header and items)? I ask because that's simpler than (say) the query needing to work even if someone adds a new property that has to be replicated to the new object. In the former case, we can just select, in the latter case we have to get tricky with replacement, because T-SQL has no convenient way to edit JSON arrays. — Jeroen Mostert
– Jeroen Mostert, Commented Jul 29, 2019 at 14:19
Yes, the header should repeat in every row, and the items should take only a single index, but still be formatted like the original object (i.e., header: data, items: [data_for_one_index]. — ang
– ang, Commented Jul 29, 2019 at 14:23

Jeroen Mostert · Accepted Answer · 2019-07-29 14:44:49Z

2

The requirement to repeat the other properties complicates this a bit, because we need to build a new object explicitly. Even so it's not too hard:

update someTable
set 
    [json] = (
        select (
            select 
                "header" = json_query(@json, '$.header'), 
                "items" = json_query(N'[' + items.item + N']')
            for json path, without_array_wrapper
        )
    ), 
    item_id = items.item_id
from openjson(@json, '$.items') with (
    item nvarchar(max) '$' as json,
    item_id varchar(50) '$.id.name',
    i int '$.id.i'
) items
join someTable on [someTable].i = items.i

Here I'm assuming the @json has already been unwrapped from its array, as your query seems to assume. If it's not, substitute $.[0] for $ in the outer query.

edited Jul 29, 2019 at 14:44

answered Jul 29, 2019 at 14:28

Jeroen Mostert

29k2 gold badges60 silver badges92 bronze badges

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

8 Comments

Jeroen Mostert Over a year ago

@Matthew: Is header actually a string, as in your example, or an arbitrary object? json_value won't work in the latter case, and json_query should be used instead (which, in turn, will not work if header is actually a value).

ang Over a year ago

Header is an arbitrary object

ang Over a year ago

On test data the select seems to be working perfectly (although I'm getting a multi-part identifier could not be bound error on items.item), but the update is failing...

ang Over a year ago

That's probably left for another question though... thanks for the help!!

Jeroen Mostert Over a year ago

I see the same issue in SSMS (18.1), but this appears to be a spurious error from Intellisense -- the actual T-SQL is sound (tested against an actual table).

|

Zhorov · Accepted Answer · 2019-07-31 06:36:55Z

Update:

It's an attempt to improve my answer (I missed the header part of the JSON content in the original answer). Of course, the @JeroenMostert's answer is an excellent solution, so this is just another possible approach. Note, that if header part of JSON content is scalar value, you should use JSON_VALUE().

Table and JSON:

-- Table
CREATE TABLE #Data (
   i int,
   [json] nvarchar(max),
   item_id nvarchar(100)
)
INSERT INTO #Data
   (i, [json], [item_id])
VALUES
   (0 , N'entire_object_at_index_0',  N'item_id'),
   (4 , N'entire_object_at_index_4',  N'item_id4'),
   (17, N'entire_object_at_index_17', N'item_id17')

-- JSON
DECLARE @json nvarchar(max) = N'[{
  "header": {"key": "some_data"},
  "items": [{
    "id": {
      "i": 0,
      "name": "item_id"
    },
    "meta": {
      "data": "some_data",
      "text": "some_text"
    }
  }, {
    "id": {
      "i": 4,
      "name": "item_id4"
    },
    "meta": {
      "data": "some_data",
      "text": "some_text"
    }
  }, {
    "id": {
      "i": 17,
      "name": "item_id17"
    },
    "meta": {
      "data": "some_data",
      "text": "some_text"
  }}]
}]'

Statement:

UPDATE #Data
SET #Data.Json = j.Json
FROM #Data
CROSS APPLY (
   SELECT
      JSON_QUERY(@json, '$[0].header') AS header,
      JSON_QUERY(j.[value], '$') AS items
   FROM OPENJSON(@json, '$[0].items') j
   WHERE JSON_VALUE(j.[value], '$.id.i') = #Data.[i]
   FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) j ([Json])

Original answer:

One possible approach is to use OPENJSON and appropriate join:

Table and JSON:

-- Table
CREATE TABLE #Data (
   i int,
   [json] nvarchar(max),
   item_id nvarchar(100)
)
INSERT INTO #Data
   (i, [json], [item_id])
VALUES
   (0 , N'entire_object_at_index_0',  N'item_id'),
   (4 , N'entire_object_at_index_4',  N'item_id4'),
   (17, N'entire_object_at_index_17', N'item_id17')

-- JSON
DECLARE @json nvarchar(max) = N'[{
  "header": "some_data",
  "items": [{
    "id": {
      "i": 0,
      "name": "item_id"
    },
    "meta": {
      "data": "some_data",
      "text": "some_text"
    }
  }, {
    "id": {
      "i": 4,
      "name": "item_id4"
    },
    "meta": {
      "data": "some_data",
      "text": "some_text"
    }
  }, {
    "id": {
      "i": 17,
      "name": "item_id17"
    },
    "meta": {
      "data": "some_data",
      "text": "some_text"
  }}]
}]'

Statement:

UPDATE #Data
SET [json] = j.[value]
FROM #Data
LEFT JOIN (
   SELECT 
      [value],
      JSON_VALUE([value], '$.id.i') AS [i]
   FROM OPENJSON(@json, '$[0].items')
) j ON (#Data.[i] = j.[i])

Collectives™ on Stack Overflow

How to join nested JSON indices to multiple rows in SQL by primary key

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related