That's not what I was asking. Let me give you an example of what I'm talking about. The second row in the original dataset is id=2, Name='B', Code=2. Now in the expected output, the second row is id=2, Name='B', Code=1. It looks like it's the same row, only its Code value is different. So what I'm asking is, do you really want to just show the rows in a specific order (based on the input), as you say in your question, or do you instead want to change the values of Code according to a specific pattern (based on the input), as your expected output seems to suggest? — Andriy M2 mins ago
Josh certainly did a good job implementing one interpretation of the question but the spec is confusing.
Unless I'm missing something and will hopefully be corrected
@George.Palacios yes though it requires direct SQL to exploit. giving direct SQL access is a bit like giving someone shell access — they will probably be able to escalate.
We connect throguh RDP to our ERP solution which uses MSSQL 2008, right now I can see on windows logs and SQL logs that we have a HUGE amount of connection attempts to sa user and results in slow connections to us, users.
Since we connect through RDP and both ERP and DB are on the same server, I...
The query language is half in DSL which you provide to OPENJSON and the other half of the json query is in the sql query itself. Moreover it cant handle some cases at all so far as I can see it -- at least not without writing a conditional self-join based on the type.
When they first introduced XML parsing in SQL Server, they gave us OPENXML, which was arguably even worse, because it required calling system stored procedures before and after using OPENXML. Then they implemented XPath and life became easier. I take it, their giving us OPENJSON essentially means they haven't settled on an established (as in "accepted and used by many, or even most, major vendors") XPath equivalent for JSON to implement in SQL Server
and i remember a bananas tvf i used to used for XML that did something similar IIRC
it seems like if you're able to unwrap the entire blob into key-values pairs you should also be able to build a "navigate to this node" path on the way there
way less intuitive than you might hope though, at least for me
it's not super apparent which node types are visible to the second argument of OPENJSON, and which need to be traversed to from the WITH block
Most of these now work. My comment below has the following markdown source:
On [main], you are expected to write proper English (as advertised on [english.se]), but here on [meta.se] it's more important to have freehand circles, so please [edit] your post, otherwise I'll have to flag you (see...
If you are strictly confident that your nested arrays will never go deeper than N levels, you can completely unwrap the array-of-arrays with N uses of APPLY. If you need to handle for arbitrary nesting levels, you can unwrap the array-of-arrays recursively using something like the following, whic...
I asked the following question on the GIS site:
https://gis.stackexchange.com/questions/328463/postgis-proper-way-to-model-object-that-follows-sections-of-another-objects-pa
Is that the best place for it, or is it better to have it on the DBA site? It feels like I might be getting a bit deep int...
I have a hierarchy that looks like this:
As JSON in TSQL it is this:
declare @Employees nvarchar(max) =
'{
"person": "Amy",
"staff": [
{ "person": "Bill" },
{
"person": "Chris",
"staff": [
{ "person": "Dan" },
{ "person": "Emma" }
]
}
]
}';
...
Most of these now work. My comment below has the following markdown source:
On [main], you are expected to write proper English (as advertised on [english.se]), but here on [meta.se] it's more important to have freehand circles, so please [edit] your post, otherwise I'll have to flag you (see...
declare @ex nvarchar(max) = '[ 7,2, [6,7], 2, 10 ]';
;WITH cte
AS (
SELECT *
FROM OPENJSON(@ex, '$') AS j1
)
SELECT c.[key], ISNULL(v.value, c.value)
FROM cte c
OUTER APPLY (
SELECT *
FROM OPENJSON(c.value, '$') AS j1
WHERE c.[type] = 4
)v
;
