[
    {
        "Level"       : "S1",
        "Major"       : "Teknik Informatika"
    },
    {
        "Level"       : "SMA",
        "Major"       : "IPA"
    },
    {
        "Level"       : "SMP",
        "Major"       : "Umum"
    },
    {
        "Level"       : "SD",
        "Major"       : "Umum"
  }
]

iD  | UserID  | Education
------------------------------------------------------------------------------
1   | B000-1  | [{"Level":"S1","Major":"TI"},{"Level":"SMA","Major":"IPA"}]
2   | B000-2  | [{"Level":"SMA","Major":"IPS"},{"Level":"SD","Major":"Umum"}]
3   | B000-3  | [{"Level":"SMA","Major":"IPA"}]
4   | B000-4  | [{"Level":"SD","Major":"Umum"}]

Jika setiap kolom table Education kurang lebih berisi seperti object diatas, bagaimana saya mencari data Education[Level]='SMA' tanpa menggunakan OPENJSON, karena saya menggunakan sql server 2012

Translate : If every column in Education table contains objects like above, how can I find Education[Level]='SMA' without using OPENJSON, because I am using SQL Server 2012

iD  | UserID  | Education
------------------------------------------------------------------------------
1   | B000-1  | [{"Level":"S1","Major":"TI"},{"Level":"SMA","Major":"IPA"}]
2   | B000-2  | [{"Level":"SMA","Major":"IPS"},{"Level":"SD","Major":"Umum"}]
3   | B000-3  | [{"Level":"SMA","Major":"IPA"}]

CodePudding user response：

Please try the following solution.

We are converting JSON data into attributes based XML. For example, for the first row it will be like follows:

<root>
    <r Level="S1" Major="TI"/>
    <r Level="NotSMA" Major="IPA"/>
</root>

After that XQuery method .exist() is used to search for the @Level parameter.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, Education NVARCHAR(MAX));
INSERT @tbl (Education) VALUES
(N'[{"Level":"S1","Major":"TI"},{"Level":"NotSMA","Major":"IPA"}]'),
(N'[{"Level":"SMA","Major":"IPS"},{"Level":"SD","Major":"Umum"}]'),
(N'[{"Level":"SMA","Major":"IPA"}]');
-- DDL and sample data population, end

DECLARE @separator CHAR(4) = '},{"'
    , @Level VARCHAR(20) = 'SMA';

SELECT * 
FROM @tbl AS t
CROSS APPLY (SELECT TRY_CAST('<root><r '   
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Education, '":', '=')
        , ',"', SPACE(1))
        , '}]', '')
        , '[{"', ''), @separator, '/><r ')   
        '/></root>' AS XML)) AS t1(c)
WHERE c.exist('/root/r[@Level=sql:variable("@Level")]') = 1;

Output

CodePudding user response：

A quick search lead me to Consuming JSON Strings in SQL Server

Here the auther shows how 'older' versions of SQL Server can read/write JSON objects/strings. The solution needs to be tailored to your needs, as your JSON structure is different, but it is possible.

Or you can brute force like .. like PM 77-1 suggested above