SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE t (
    DataID int,
    SomeForeignKey int,
    SpecificDataValues VARCHAR(100),
    OtherSpecificDataValues VARCHAR(100)
);
INSERT INTO t VALUES
(0, 1, '["1", "2"]', '["1", "2"]');
1 rows affected

SELECT DataID, SomeForeignKey, j1.value, j2.value
FROM t
CROSS APPLY OPENJSON(SpecificDataValues) AS j1
CROSS APPLY OPENJSON(OtherSpecificDataValues) AS j2
WHERE j1.[key] = j2.[key]


DataID
SomeForeignKey
value
value


0
1
1
1

0
1
2
2


SELECT DataID, SomeForeignKey, j1.value, JSON_VALUE(OtherSpecificDataValues, CONCAT('$[', CAST(j1.[key] AS INT), ']'))
FROM t
CROSS APPLY OPENJSON(SpecificDataValues) AS j1


DataID
SomeForeignKey
value
(No column name)


0
1
1
1

0
1
2
2